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

 

[graemeal]

sory about this but how do you add multiple ones in a work

 

[jeffreybrown]

This is not very clear at least to me.

You want to add multiple what? Do you mean macors? Shortcut keys to those macros?

Some more detail here would help

 

[graemeal]

Sorry about my lack of clarity. I was wondering how to put say 7 of the below within a worksheet, all with differant formulas. Or do I have to put them in a module if I need several variations.

I have an example that I tried below with 2 but it does not work. I just changed the Macro9() to 8 but as I said obviously incorrect.

Regards

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

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

 

[jeffreybrown]

If you have different formulas for different situations then you could use multiple If statements.

For 2003 you can nest up to 7 if statements and 2007+ I believe it is 64.

This is what it would look like =IF(AND(H2>-0.01,C2>1000),D2/C2,IF(AND(H2>-0.01,C2>75),D2/C2,0)).

Unless I am missing something obvious, your value for True would happen no matter the value of C2 as long as it is greater than 75 and H2 is met. So the order would not matter.

The only difference between the two formulas is the second half of the AND statement. If C2 > 1000 do D2/C2 but at the same time If C2 > 75 do D2/C2.

Isn't this the same thing?

 

[graemeal]

Thank you for you effort. I am not very savvy with macros (an understatement) but maybe I will explain what I want to happen. I want to be able to change the formulas in 2 columns.

As in press CTR W and have the formulas change to =IF(AND(H3>-0.01,C3>1000),D3/C3,0) in column J and =IF(AND(H3>-0.01,C3>1000),Y3/Z3,0) in column L

Then later on during the day I want to say hit CTR S and have say
=IF(AND(C2>10),D2/C2,0) in column J =IF(AND(C2>10),Y2/Z2,0)

And so on with another 5 variations.

It is a sheet that tracks the stock market so by pressing CTR CTR W I want to see stocks that have risen more that -0.01 (zero and over) and only ones that have done more than 1000 trades

In the next lot I want to see stocks that have done more than 10 trades and then another 5 variations

So now all is OK with one in the worksheet but how do I have multiple variations within the worksheet so i just press 2 buttons to change to diff formulas.

Thanks again I hope I am at least semi clear

 

[jeffreybrown]

In post #13, the two macro's can each have their own shortcut.

Select Alt + F8 >> Highlight the macro >> Options >> Assign a shortcut key.

Another option: use a Worksheet_Change Event

In A1 or any available cell, build a data validation list with your various options/choices.

When you pick one of those options/choices the Worksheet_Change will fire and the Case Statement takes over. If the value selected is First then the first two sets of formulas or if Second then the next two and so on.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Target.Parent.Range("A1")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    
    Select Case Target.Value
    
    Case "First"
    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)"
    
    Case "Second"
    
    Range("J2:J1839").FormulaR1C1 = "=IF(AND(RC[-2]>-0.01,RC[-7]>75),RC[-6]/RC[-7],0)"
    Range("L2:L1839").FormulaR1C1 = "=IF(AND(RC[-4]>-0.01,RC[-9]>75),RC[13]/RC[14],0)"
        
    'Etc...
    
    Case Else
    
    End Select
End Sub

 

[graemeal]

Thank you so much I will play around with the info.

Kind regards

G.

 

[jeffreybrown]

Ok, please post back if you need more help.

 

[graemeal]

I finally get the picture. Thank you kindly again. One more question if I may. Can I display the formulas as alphabet and row formats or do I have to use R1C1 reference style. I only used that as an example because that is the way it was displayed when I recorded the macro.

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

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

 

[graemeal]

Sorry for so many posts. Simply removed the R1C1 and all good now without the R1C1 format. Quite obvious when you think about it.

Again for the 10th time, much appreciated for all your effort.

Kind Regards

Graeme