VBA to detect next empty cell in column and add formula, then repeat for next column, ...

[Marijke]

Hi,

I'm working on automatically populating data into a report, and just when i thought I figured it out, it doesn't seem to work....
I have 7 columns where, at the bottom of each folumn - in the first empty cell, I need to enter a formula.

I've come up with the below, but the repetative 'Dim Lastrow as Long' is not appreciated by Excel.
Also, for some reason it is not shifting columns, while it should...... (all formula's are entered on column A)

Anyone have any brilliant thoughts on this one?

Please Login or Register  to view this content.

[HaHoBe]

Hi, Marijke,

as you have noted it´s good enough to once Dim the variable. The reason for not shifting is the use of ActiveCell in every formula which should be inserted.

Your code may look like this:

Please Login or Register  to view this content.

Inserting a volatile Function like Now() as a formula might not show the result I would expect from it as the DateTimeStamp would update on any calculation. As the formulas should be inserted into the very same row you may use a code like

Please Login or Register  to view this content.

Ciao,
Holger

[Marijke]

Thanks Holger,
I've used the 2nd code you have put in and my report runs perfectly!