Can these 2 simple macro be written better?

[Mathman]

Looking to see better variation for running these macros.

If you can help that would be great!

1-

Sub Calculation()



shtEarnings.Select
Range("H3").Select
ActiveCell.FormulaR1C1 = "=RC[7]/RC[9]-1"
Selection.AutoFill Destination:=Range("H3:H52")


End Sub




2-


Sub Max ()


shtEarnings.Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "=MAX(<wbr>MostRecentQuarterFinancials)"
Range("E1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


End Sub

 

[Caleeco]

Hello Mathman,

These are untested, but something like this may cut your lines of code a bit:

Sub Calculation()
    'Assuming shtEarnings has been defined somewhere
    shtEarnings.Range("H3:H52").FormulaR1C1 = "=RC[7]/RC[9]-1"
End Sub


Sub Max()
    'Assuming shtEarnings has been defined somewhere
    With shtEarnings.Range("E1")
        .FormulaR1C1 = "=MAX(MostRecentQuarterFinancials)"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
End Sub

Let me know if they work
Thanks
Caleeco

 

[JLGWhiz]

Here is a combined version.

Sub Calc_Max()
 With shtEarnings
     .Range("H3").FormulaR1C1 = "=RC[7]/RC[9]-1"
     .Range("H3").AutoFill Destination:=Range("H3:H52")
     .Range("E1").FormulaR1C1 = "=MAX(MostRecentQuarterFinancials)"
     .Range("E1").Copy
     .Range("E1").PasteSpecial Paste:=xlPasteValues
 End With
End Sub

BTW, The words 'Calculation' and 'Max' are keywords used in VBA and Excel respectively. They have specific meaning when used properly in code and it is best to avoid using them as macro titles or variable names when constructing macros. They could cause confusion during the compile activity and yield undesirable results.

 

[Mathman]

With shtEarnings.Range("E1")
.FormulaR1C1 = "=MAX(MostRecentQuarterFinancials)"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End Sub


This one works, thank you!. If I had 3 sheet names for the same data to be inputed do I have to write the code out 3 times?

 

[Rick Rothstein]

Here is a combined version.

Sub Calc_Max()
 With shtEarnings
     .Range("H3").FormulaR1C1 = "=RC[7]/RC[9]-1"
     .Range("H3").AutoFill Destination:=Range("H3:H52")
     .Range("E1").FormulaR1C1 = "=MAX(MostRecentQuarterFinancials)"
     .Range("E1").Copy
     .Range("E1").PasteSpecial Paste:=xlPasteValues
 End With
End Sub

You can compact that some more...

Sub Calc_Max()
  With shtEarnings
    .Range("H3:H52").FormulaR1C1 = "=RC[7]/RC[9]-1"
    .Range("E1").Value = [MAX(MostRecentQuarterFinancials)]
 End With
End Sub

If the OP needs these as two separate macros (like he originally had), then these would work for him...

Sub Calculation()
  shtEarnings.Range("H3:H52").FormulaR1C1 = "=RC[7]/RC[9]-1"
End Sub


Sub Max()
  shtEarnings.Range("E1").Value = [MAX(MostRecentQuarterFinancials)]
End Sub

 

[Mathman]

They both work as expected. Thank you again, it really cleans up the codes!

 

[Rick Rothstein]

They both work as expected. Thank you again, it really cleans up the codes!

Who are you replying to? If not me, did you see what I posted in Message #5 yet?

 

[Mathman]

Hi Rick

That's great. So I can use the same sheet name and under it add all code refrencing this sheet and I assume the end paste must also always be the same for this to work? If I had 2 pastes, 1 transpose true, the other false I assume I have to have 2 separate Sub Calcs or can I add the code that has a transpose paste true first and the paste then continue with the other code then add the paste that is false?

 

[Rick Rothstein]

Hi Rick

That's great. So I can use the same sheet name and under it add all code referencing this sheet and I assume the end paste must also always be the same for this to work? If I had 2 pastes, 1 transpose true, the other false I assume I have to have 2 separate Sub Calcs or can I add the code that has a transpose paste true first and the paste then continue with the other code then add the paste that is false?

The code I posted is complete unto itself... there is no selecting, copying or pasting with it... I am assigning what you want directly to the ranges. Try the code exactly as I posted it (assuming shtEarnings is the code name for your worksheet) and you will see it works directly (if that is not the code name for your sheet and is a variable instead, then you omitted vital information in your post). As for other sheets, if you tell me their sheet names (not code names) are, I'll write a loop for you. I do have a concern about your MostRecentQuarterFinancials defined name (that is a defined name, correct?) as it would be (I guess) sheet specific and, as such, not work on other worksheets.

 

[Mathman]

With the formulae is their a way to get the results only and not the formulae? so I could avoid so many pastes?