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