0

So this macro inserts 2 columns in C and D. Then fills this formula all the way down column C.

Option Explicit

Sub Format_dates()

    Dim ws As worksheet
    Dim lst As Long
    Dim i As Long
    Dim r As Long

    Columns("C:D").Insert Shift:=xlToRight
    Range("C2").Value = "START DATE"
    Range("D2").Value = "END DATE"

    Range("A" & Cells.Rows.Count).End(xlUp).Offset(0, 3).Select

    With ActiveSheet ' ("MASTER Business Events Calendar")

        lst = ws.Cells(.Rows.Count, 2).End(xlUp).row

        For i = 3 To lst
            r = Cells.row
            ws.Cells(i, 3) = "=IF(ISNUMBER(B&r),B&r,DATEVALUE(TRIM(LEFT(B&r,FIND("-",SUBSTITUTE(B&r,CHAR(150),"-")&"-")-1))&", "&RIGHT(B&r,4)))"
        Next i
    End With

End Sub

Can I not substitute variables in formulas like I do with python?

Julian
  • 411
  • 4
  • 18

1 Answers1

1

You need to separate the Variable from the string:

B&r

Should be

B" & r & "

And all " should be ""

ws.Cells(i, 3).Formula = "=IF(ISNUMBER(B" & r & "),B" & r & ",DATEVALUE(TRIM(LEFT(B" & r & ",FIND(""-"",SUBSTITUTE(B" & r & ",CHAR(150),""-"")&""-"")-1))&"", ""&RIGHT(B" & r & ",4)))"

Another note, You refer to ws but never set it. It should be set like

Set ws = ActiveSheet

Then remove the With block.

That being said the loop is not needed.

ws.Range(ws.Cells(3, 3),ws.Cells(lst,3)).Formula = "=IF(ISNUMBER(B3),B3,DATEVALUE(TRIM(LEFT(B3,FIND(""-"",SUBSTITUTE(B3,CHAR(150),""-"")&""-"")-1))&"", ""&RIGHT(B3,4)))"

The row, since it is relative, will iterate correctly

Option Explicit

Sub Format_dates()

    Dim ws As worksheet
    Set ws = ActiveSheet


    ws.Columns("C:D").Insert Shift:=xlToRight
    ws.Range("C2").Value = "START DATE"
    ws.Range("D2").Value = "END DATE"

    Dim lst As Long
    lst = ws.Cells(ws.Rows.Count, 2).End(xlUp).row

    ws.Range(ws.Cells(3, 3),ws.Cells(lst,3)).Formula = "=IF(ISNUMBER(B3),B3,DATEVALUE(TRIM(LEFT(B3,FIND(""-"",SUBSTITUTE(B3,CHAR(150),""-"")&""-"")-1))&"", ""&RIGHT(B3,4)))"


End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Would there be a time when I need to use a for loop for entering formulas? I was learning python (since it is easier) and applied that logic to vba. And is it always relative? If I wanted absolute, I would put $ within the formula, correct? – Julian Jul 26 '19 at 18:23
  • If you are entering a formula then use the `$` to make it absolute and just apply it to the full range at once to iterate the relative portion. Array formulas are different and are applied differently depending on use. – Scott Craner Jul 26 '19 at 18:25