Changing the formulas in 2 columns with one action

[graemeal]

Hi, I have these 2 macros, is there any way of adding some code to trigger both with one action. I need to change the formulas in 2 columns constantly and would like to do it with one action. I have 7 actual formula changes that I constantly switch back and forth to.

Thanks

Sub Macro9()
'
' Macro9 Macro
'
' Keyboard Shortcut: Ctrl+d
'
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>-0.01,RC[-7]>1000),RC[-6]/RC[-7],0)"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J1839")
    Range("J2:J1839").Select
    Range("A2").Select
End Sub

Sub Macro10()
'
' Macro10 Macro
'
' Keyboard Shortcut: Ctrl+c
'
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-4]>-0.01,RC[-9]>1000),RC[13]/RC[14],0)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L1839")
    Range("L2:L1839").Select
    Range("A2").Select
End Sub

 

[sulakvea]

hi, here's one piece that does both - seems like the macros were recorded, so i cleaned it up a little, removing extra lines

Sub AutoFl()

Range("J2").Formula = "=IF(AND(H2>-0.01,C2>1000),D2/C2,0)"
Range("J2").AutoFill Destination:=Range("J2:J1839")
Range("L2").Formula = "=IF(AND(H2>-0.01,C2>1000),Y2/Z2,0)"
Range("L2").AutoFill Destination:=Range("L2:L1839")

End Sub

 

[hiker95]

graemeal,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub Macro910()

Range("J2").FormulaR1C1 = "=IF(AND(RC[-2]>-0.01,RC[-7]>1000),RC[-6]/RC[-7],0)"
Range("J2").AutoFill Destination:=Range("J2:J1839")

Range("L2").FormulaR1C1 = "=IF(AND(RC[-4]>-0.01,RC[-9]>1000),RC[13]/RC[14],0)"
Range("L2").AutoFill Destination:=Range("L2:L1839")

End Sub

 

[jeffreybrown]

How about...

Sub Macro9()
    Range("J2:J1839").FormulaR1C1 = "=IF(AND(RC[-2]>-0.01,RC[-7]>1000),RC[-6]/RC[-7],0)"
    Range("L2:L1839").FormulaR1C1 = "=IF(AND(RC[-4]>-0.01,RC[-9]>1000),RC[13]/RC[14],0)"
End Sub

 

[hiker95]

jeffreybrown,

Nice.

I was just going to update my code.


graemeal,

When you are using R1C1, you do not have to AutoFill.

 

[jeffreybrown]

I picked this up from Norie many moons ago.

 

[graemeal]

Thank yoy kindly

Excuse my ignorance but how is the code activated. Is there a way it activate it from 1 key?

Thanks again

 

[graemeal]

Sorry I meant is there a way to activate with say the F keys. As in with one key. Ctr 9 works like a dream so all good, one key would be better. Thank you again. Fantastic

 

[jeffreybrown]

Put the macro in a module

Alt + F8 >> Highlight the macro >> Options

This is where you can set a shortcut key

If you can do it with one key I'm not sure.

 

[graemeal]

Thank you again. Just made my life a whole lot easier.

Kind Regards