Issue with adding formula to a excel macro

Turbulentsky · Feb 12th 2019

Greetings all,

i am trying to create a basic macro for some of my guys here at work. i am looking to add in a formula in J2 so that i can copy it down J as a whole. however, when i try to add in the formula i am getting a compileing error stating my statement is not ended after the first Good outcome. any assistance would be appreciated.

Code

Sub TestMacro2()
'
' TestMacro2 Macro
'


'
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.ColumnWidth = 35.71
    Cells.EntireRow.AutoFit
    Range("C5").Select
    Columns("C:C").ColumnWidth = 15.14
    Columns("D:D").ColumnWidth = 12.14
    Columns("E:E").ColumnWidth = 11
    Columns("F:F").ColumnWidth = 11.29
    Columns("G:G").ColumnWidth = 11.57
    Columns("H:H").ColumnWidth = 4.57
    Columns("I:I").ColumnWidth = 3.57
    Range("J2").Formula = "=IF(F2>=(TODAY()+31),"Good",IF(AND(F2<=(TODAY()+30),(F2>=(TODAY()+1))),"Due Soon",IF(G2<>"","Good","Over Due")"
    
    Selection.AutoFill Destination:=Range("J2:J36"), Type:=xlFillDefault
    Range("J2:J36").Select
    Range("G6").Select
    ActiveWindow.SmallScroll Down:=-27
    Range("G2:G36").Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.NumberFormat = "m/d/yyyy"
    ActiveWindow.SmallScroll Down:=-33
    Range("F2:F36").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("J20").Select
End Sub

Display More

**Luke M** · Feb 13th 2019

You need a way to tell the VBA that you're not ending the string, so we use double quotation marks to indicate that "yes, I want a quotation mark here". I also went through and optimized things a little bit

Code

Sub TestMacro2()
    'Make some variables
    Dim strForm As String


    'turn off screen flashing
    Application.ScreenUpdating = False
    
    Rows("1:1").Delete Shift:=xlUp
    Cells.ColumnWidth = 35.71
    Cells.EntireRow.AutoFit
    
    Columns("C:C").ColumnWidth = 15.14
    Columns("D:D").ColumnWidth = 12.14
    Columns("E:E").ColumnWidth = 11
    Columns("F:F").ColumnWidth = 11.29
    Columns("G:G").ColumnWidth = 11.57
    Columns("H:H").ColumnWidth = 4.57
    Columns("I:I").ColumnWidth = 3.57
    
    'What's the formula? Note the use of double quotation marks so we don't end too early
    strForm = "=IF(F2>=(TODAY()+31),""Good"",IF(AND(F2<=(TODAY()+30),(F2>=(TODAY()+1))),""Due Soon"",IF(G2<>"""",""Good"",""Over Due"")"
    'Write the formula all at once
    Range("J2:J36").Formula = strForm


    Range("G2:G36").Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F2:G36").NumberFormat = "m/d/yyyy"
    
    Application.ScreenUpdating = True
End Sub

Display More

Turbulentsky · Feb 13th 2019

Asome Luke M thanks for the update and information.. works like a charm.

Issue with adding formula to a excel macro

Participate now!

Share