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))"