Okay so this is quite possibly really simple, but I'm stumped.

I'm doing some Excel VBA macros, and I need to create cell formulas on the fly.

Currently I fill cells using numeric Cell notation, aka

Worksheet(1).Cells(8,9).Value = "=sum(A1:A10)"

Worksheet(1).Cells(8,9).Value = "=sum("+ Worksheet(1).Cells(1,1) + ":" + Worksheet(1).Cells(10,1) + ")"

12/12/2006 2:22:11 PM

feels like the INDIRECT method should come in handy here.
.... i'm not sure how, but this is the kind of thing it does in Excel. Unfortunately, there's no direct equivalent in VBA

12/12/2006 2:56:13 PM

Figured it out.

Just had to use the R1C1 notation, excel auto-converts it to A1 depending on which option is set.

Thanks Agent, you jogged my mind and got me on the right track `

12/12/2006 3:24:02 PM

Every Excel Range class has a .Address method and an .AddressR1C1 method:

Cells(1,1).Address will give you "$A$1".

But if you want to refer to a range like: "Column 1, Row 1 through Column 2 Row 2", try this:

Sub DoSomething()
    Cells(1,1).Formula = "=sum(" rowcol(1,1,2,2) & ")"
End Sub

Function rowcol$(StartRow&, StartCol&, EndRow&, EndCol&)

    rowcol = Mid$(Columns(StartCol).Address, 2, 1) & StartRow & ":" & _
             Mid$(Columns(EndCol).Address, 2, 1) & EndRow

End Function

12/12/2006 3:31:09 PM

^Nice dude, EXACTLY what I need, this actually just made all my stuff that much easier, thanks!

12/12/2006 3:42:05 PM