Use count data in formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pam - 13 Mar 2008 18:38 GMT

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

Reply to this Message

pam - 13 Mar 2008 19:42 GMT

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

> 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
[quoted text clipped - 12 lines]
> "=SUM(('" & ws & "'!R2C8:R" & numrowsdata & "C8=RC[-1])*('" & ws &
> "'!R2C6:R" & numrowsdata & "C6))"

Reply to this Message