Macro to copy formula to blank cells in a dynamic range

[masben]

Hi ,

I have this macro to copy the formual in a certain cell (F1 in the example below) to all blank cells in a range

Please Login or Register  to view this content.

There are three problems:

1. if the range contains no blank cells I recive a warning "no blank cells" and the full range stays marked - resulting in the formula in F1 to be copy over all cells.
2. the range change so in the macro I need to refer to a dynamic range and I don't know how?
3. I run the above macro for several colums (F,G,H,I,J and K) - It would be great if one macro could do this for all colums in once. (the number of rows won't change between colums). I could to this by just copy the code within the macro and paste it for each colum but I bet there are a more fancy way of doing this :-)

Thanks in advance for your help!

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[masben]

Hi John,

Thanks for your reply - I havn't tried it yet but it looks like it lacks a feature that I need. I wan't clear enough about this so its my fault - the blank cell in each colum should be replaced by the formula in row 1 in that colum. So in Col F it's the formula in F1 in col G it's the formula in G1 and so on... Do you think it's possible to modify your code to meat this?

Thanks agian,
Martin

[JOHN H. DAVIS]

Try this:

Please Login or Register  to view this content.

[Jacc]

masben, it seems almost like you are copying the Table function in Excel.

[masben]

Thanks John!

Tha macro works like a charm! Perfect.

All the best
/Martin

[masben]

Hi Jacc,

How do you mean? - I like to learn :-)

/martin

[JOHN H. DAVIS]

Thanks John!

Tha macro works like a charm! Perfect.

All the best
/Martin

You're welcome. Glad to help out, and thanks for the feedback.

[Jacc]

I made a simple table here. Just fill in new numbers in the Diameter and Height columns and it will copy the formulas automatically.

[masben]

I made a simple table here. Just fill in new numbers in the Diameter and Height columns and it will copy the formulas automatically.

Thanks Jacc,

It's not exactley what I needed this time but very useful and will come well in hand other times.

Cheers,
Martin