copying formulas in autofilter mode

J

jabberwhacky

Hi I have two adjacent columns and I copy the formula from one column
to another using something like -


Range("Column1").Formula = Range("Column2").Formula
with some arbitrary formula like say -

=IF(EXACT($A119,"market order"),$P119,"")

This works fine as long as I havent filtered values in the auto filter.

However, when I choose a filter on another column the range copy
statement ends up referencing the wrong cells in the copied column

so, If my filter hides row 1 to 100,

the formula statement results the cell at row 119 for "Column1" having
the formula

=IF(EXACT($A19,"market order"),$P19,"")

Is there a way I can get around this?
 
Top