How to sum variable number of records

[henio]

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&quot.Select
Range("A1&quot.Select
Selection.FormulaArray = "=SUM(VALUE(Sheet1!R[1]C[12]:R[27]C[12]))"
Range("B1&quot.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

 

[SkipVought]

Henio,

I never liked R1C1 notation. I'd ruther use

Cells(r, c).Address(True)

which returns something like

Sheet1!A1

For your particular question

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

http://www.TheOfficeExperts.com

 

[henio]

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

 

[SkipVought]

The R1C1 references are relative to the selected cell.

Substitute

    .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

http://www.TheOfficeExperts.com

 

[henio]

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