Putting formula into column from macro?

S

Samuel

How do I load a formula into a column from a macro?
I tried:
ActiveSheet.Cells(6, 6) = "=SUM(H:H)"
But it doesn't work.....
Also, is there an object that returns the number of rows in a sheet? So
I can loop until I reach that number?

Suggestion...Please!
 
B

Bernie Deitrick

Samuel,

ActiveSheet.Cells(6, 6).Formula = "=SUM(H:H)"

Rows.Count will return the number of rows in the sheet, but it is likely that you really mean the
number of used rows.

Sub Test()
Dim i As Long
For i = 1 To Cells(Rows.Count, 4).End(xlUp).Row
Cells(i, 3).Value = "Filled in by the macro"
Next i
End Sub

Will loop to fill in column C to match column D....

HTH,
Bernie
MS Excel MVP
 
D

Dave Peterson

Excel is pretty forgiving. I would have used something like:

ActiveSheet.Cells(6, 6).formula = "=SUM(H:H)"
or
with ActiveSheet.Cells(6, 6)
.numberformat = "General"
.formula = "=SUM(H:H)"
end with

Your code worked fine for me when I tested it. But I'm not sure that answers
your real question. What do you mean be load a formula into a column?

And for your second question, if I know my data, I like to pick out a column
that always has data in it if that row is used. Then I can use something like:

Dim LastRow as Long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

I used column A in my example. It's like going to A65536 and hitting the end
key followed by the up arrow.
 
Top