Use count data in formula
 

I would like to enter a formula in column J that refers to a variable range
in column a. I think I'm going about it in the wrong way. Everytime you run
the macro, it updates data from an XML file and creates a new worksheet with
today's date. Can you help this beginner?

Dim numrowsdata As Integer
Dim ws As String

numrowsdata = Selection.Rows.Count
ws = ActiveSheet.Name

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("j7").FormulaArray = _
"=SUM(('" & ws & "'!R2C8:R" & numrowsdata & "C8=RC[-1])*('" & ws &
"'!R2C6:R" & numrowsdata & "C6))"

Use count data in formula
 

I finally got it:

Dim numrowsdata As Integer
Dim ws As String

numrowsdata = Cells(Rows.Count, "a").End(xlUp).Row
ws = ActiveSheet.Name

Range("A1").Select
Range("j7").FormulaArray = _
"=SUM(('" & ws & "'!r2c8:r" & numrowsdata & "c8=rc[-1])*('" & ws &
"'!r2c6:r" & numrowsdata & "c6))"

End Sub

"Pam" wrote:

I would like to enter a formula in column J that refers to a variable range
in column a. I think I'm going about it in the wrong way. Everytime you run
the macro, it updates data from an XML file and creates a new worksheet with
today's date. Can you help this beginner?

Dim numrowsdata As Integer
Dim ws As String

numrowsdata = Selection.Rows.Count
ws = ActiveSheet.Name

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("j7").FormulaArray = _
"=SUM(('" & ws & "'!R2C8:R" & numrowsdata & "C8=RC[-1])*('" & ws &
"'!R2C6:R" & numrowsdata & "C6))"