Applying formula only to visible rows.

I have some very complex formulas which I normally apply from the top available row (i.e. row 2) of column to the last row using the following code:

Please Login or Register  to view this content.

It works fine, however I would like to do the same for only the visible rows as advanced filtering may be used, I believe I would need such a loop:

Please Login or Register  to view this content.

The problem is that I am unable to convert the formula from step 1 (it is quite complex) to be used in step 2. Is there a method to have the formula from step 1 to be used in step 2?

Thanks a lot.

in principle substitute the 2 (row #) for rngcell.row # and the 1 for rngcell.row-1 #

Please Login or Register  to view this content.

Thanks rcm,

Just to check, are you sure the correct syntax to replace

Please Login or Register  to view this content.

is

Please Login or Register  to view this content.

?

I now end up with the following formulas, though I end up with a runtime error 438 when running it:

Please Login or Register  to view this content.

Yes, rngcell.row will give the row in which rngcell resides, maybe that VBA can't handle so many variables.. before that try to see what string comes out of it since it is a string..

Hi rcm,

Not sure about the syntax you provided, it does not seem to work for a loop, even one which contains a very basic formula.


Hi Izandol,

You are spot on, the code seems to work perfectly and it is the easiest way for me to "transpose" my formulas already written. I just need to do a sanity check as the formulas are quite complex, but at first glance everything appear to work fine. Will close the thread once I have finished my check.

Thanks a lot.