Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to sum variable number of records

Status
Not open for further replies.

henio

MIS
Jun 25, 2003
60
GB
Hi all,

I regularly receive an Excel file from a host application where all numbers are sent as text. I need to total some of the columns and put the results on a new sheet. I would like this facility to be a macro.

I recorded a macro and it gave me the following:

Sub Macro1()

Sheets("Sheet2").Select
Range("A1").Select
Selection.FormulaArray = "=SUM(VALUE(Sheet1!R[1]C[12]:R[27]C[12]))"
Range("B1").Select
Selection.FormulaArray = "=SUM(VALUE(Sheet1!R[1]C[12]:R[27]C[12]))"
End Sub

The file I receive can have any number of rows, but I can't figure out how to change this code.

I'm also puzzled that the two array formulas in A1 and A2 are different but the recorded code is the same.

i.e.

A1 contains {=SUM(VALUE(Sheet1!M2:M28))}
B1 contains {=SUM(VALUE(Sheet1!N2:N28))}

Cheers,
Henio
 
Henio,

I never liked R1C1 notation. I'd ruther use
Code:
Cells(r, c).Address(True)
which returns something like
Code:
Sheet1!A1
For your particular question
Code:
Sub Macro1()
  With Sheets("Sheet2")
    .Range("A1").Formula = "=SUBTOTAL(9,Sheet1!M:M)"
    .Range("B1").Formula = "=SUBTOTAL(9,Sheet1!N:N)"
  end with
End Sub
:)

Skip,
Skip@TheOfficeExperts.com
 
Hi Skip,

I share your dislike of the R1C1 format, and don't understand what the recorder gave me. Anyhow, your macro didn't work. Because the data is text, I had to put the value function into my formula and make it an array formula.

Cheers,
Henio
 
The R1C1 references are relative to the selected cell.

Substitute
Code:
    .Range("A1").FormulaArray = "=sum(Value(Sheet1!M:M))"
What kind of data are you summing? I used the SUBTOTAL function to be able to sum in a range containing non-numeric values.

???

Skip,
Skip@TheOfficeExperts.com
 
Hi Skip,

the data is exported from a host system as text - even the numbers. There's no point in pasting an example here because a number 3 looks like a text 3.

I started creating sheets of =value formulas and totalling those before remembering about the array formula.

Anyhow, your new construct works, although I changed it to exclude row 1 (text column headings). Thanks for the advice.

Henio
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top