Putting formula into column from macro?

[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!

[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


"Samuel" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[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.



Samuel wrote:
>
> 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!

--

Dave Peterson