I have a column with data, how do I feel the adjacent columns with formulas

Posted by Steven on February 02, 2002 3:45 PM

Say I have data in column A for N rows, where N changes depending on the data run. I have a formula I want to go in column B for N rows, without having to manually cut and paste it in column B.

Posted by Tom Urtis on February 02, 2002 4:11 PM

Try attaching this subroutine to a button on your worksheet, or maybe assign a keyboard shortcut to it.

Modify the code for where the data begins in row A (here I have A2), and of course place your formula between the quotes where indicated.

Sub CopyFormula()
Range([A2], [A65536].End(xlUp)).Offset(0, 1).Formula = "YourFormula"
End Sub

Tom Urtis

Posted by Steven Woo on February 02, 2002 8:57 PM

Thank you so much. This saved me a ton of copying and pasting. I had to make one correction here(Need the @ sign to force evaluation), but after you got me started it felt wonderful playing around with the visual basic tools until it worked.

Also for readers note that's not X(one)Up it's XLUP.

Sub TEST()
Range([g3], [g65536].End(xlUp)).Offset(0, zzz).Formula = "@if( g3 >210, i2, g3)"
End Sub
where zzz is the column offset