excel vba autofill (fill down) formula error

[2Took]

Trying to have VBA fill in a formula in the range, but getting below error...

Sub GFormula()
Range("G10:G27").Formula = "=IF(B10="","",IF(OR(B10="LABOR - CU",B10="TRVL - CU"),"HOURS","EACH"))"
End Sub

 

[2Took]

Trying to have VBA fill in a formula in the range, but getting below error...

Sub GFormula()
Range("G10:G27").Formula = "=IF(B10="","",IF(OR(B10="LABOR - CU",B10="TRVL - CU"),"HOURS","EACH"))"
End Sub

View attachment 76046

Solved by Macro Recording.

 

[Peter_SSs]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[2Took]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Thanks @Peter_SSs,

Here's the result of Macro Recording:

Sub GFormula()

    Range("G10").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-5]="""","""",IF(OR(RC[-5]=""LABOR - CU"",RC[-5]=""TRVL - CU""),""HOURS"",""EACH""))"
    Range("G10").Select
    Selection.AutoFill Destination:=Range("G10:G27"), Type:=xlFillDefault
    Range("G10:G27").Select
    Range("B10").Select

End Sub

 

[Joe4]

As you can see, the issue is that in VBA, double-quotes are used as text qualifiers. So if you want literal double-quotes in your formulas, you need to double them up in VBA.
This can sometimes get a little messy. I usually let Excel figure it out by using the Macro Recorder, just like you did! Then you don't need to worry if you have written it correctly in VBA.

 

[Peter_SSs]

Thanks @Peter_SSs,

Here's the result of Macro Recording:

Thanks for providing the code. Although the macro recorder can be great for getting a start with your code and something that does work, it can also be rather 'clumsy' code.
Mostly with recorded code you will see a lot of '.Select ...' and 'Selection. ...'. Whilst that generally works, you rarely need to actually select things in Excel to work with them and selecting can also slow your code considerably. So, after you record your code it is a good idea to see if you can remove most or all of the 'selections'

A good example in your code is this type of structure where the recorder gives you

Range("G10").Select
Selection.AutoFill Destination:=Range("G10:G27"), Type:=xlFillDefault

Commonly you can remove the Select and the Selection and combine those two lines into one like this

Range("G10").Select
Selection.AutoFill Destination:=Range("G10:G27"), Type:=xlFillDefault

Range("G10").AutoFill Destination:=Range("G10:G27"), Type:=xlFillDefault

If you are interested though, all of the lines in your procedure can actually be condensed to a single line since you can enter all the formulas at once rather than entering one and then filling down.

Sub GFormula_v2()
   Range("G10:G27").FormulaR1C1 = "=IF(RC[-5]="""","""",IF(OR(RC[-5]=""LABOR - CU"",RC[-5]=""TRVL - CU""),""HOURS"",""EACH""))"
End Sub

 

[2Took]

Thanks for providing the code. Although the macro recorder can be great for getting a start with your code and something that does work, it can also be rather 'clumsy' code.
Mostly with recorded code you will see a lot of '.Select ...' and 'Selection. ...'. Whilst that generally works, you rarely need to actually select things in Excel to work with them and selecting can also slow your code considerably. So, after you record your code it is a good idea to see if you can remove most or all of the 'selections'

A good example in your code is this type of structure where the recorder gives you

Range("G10").Select
Selection.AutoFill Destination:=Range("G10:G27"), Type:=xlFillDefault

Commonly you can remove the Select and the Selection and combine those two lines into one like this

Range("G10").Select
Selection.AutoFill Destination:=Range("G10:G27"), Type:=xlFillDefault

Range("G10").AutoFill Destination:=Range("G10:G27"), Type:=xlFillDefault

If you are interested though, all of the lines in your procedure can actually be condensed to a single line since you can enter all the formulas at once rather than entering one and then filling down.

Sub GFormula_v2()
   Range("G10:G27").FormulaR1C1 = "=IF(RC[-5]="""","""",IF(OR(RC[-5]=""LABOR - CU"",RC[-5]=""TRVL - CU""),""HOURS"",""EACH""))"
End Sub

Awesome!
Thank you, @Peter_SSs for your thoughtful guidance.

 

[Peter_SSs]

You're welcome. Glad to help.