Search code examples
excelvbaif-statementforeachboolean

VBA Triggering IF-Statement containing Left()

I'm trying to dynamically concat 2 cells together under particular circumstances and I'm having trouble getting any of my formula changes to trigger. If column A reads "Yes" and the corresponding event name in column B starts with a year and the letter "C", I want to concat the corresponding Date, a space, and the Event name into column D. Ex. "2/22/2022 2022 C00EB24HNA"

In the attached screenshot, I've highlighted the rows that should be concatenated.

Screenshot

Here is the For Each loop I'm using. I keep having the issue at the second if statement, "If "left(RC[1...."

    Dim Response As Range
    For Each Response In Selection
        If Response = "Yes" Then
            If "left(RC[1], 6)" = "2020 C" Or _
                "left(RC[1], 6)" = "2021 C" Or _
                "left(RC[1], 6)" = "2022 C" Or _
                "left(RC[1], 6)" = "2023 C" Or _
                "left(RC[1], 6)" = "2024 C" Or _
                "left(RC[1], 6)" = "2025 C" Then
                
                ActiveCell.Offset(0, 3).Select
                ActiveCell.FormulaR1C1 = _
                        "=(CONCAT(RC[-1],"" "",RC[-2]))"
                ActiveCell.Offset(-1, -3).Select
            Else
            End If
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Next Response

There's probably an issue with my syntax but I'm not sure how to fix it. Any guidance would be greatly appreciated! Thank you!

Solution

  • Try this code (assuming that you use US version of Excel, if not, please use the proper local date format):

    Sub ConcatTest()
Dim Response As Range
    For Each Response In Selection
        With Response
            If .Value = "Yes" Then
                If Left(.Offset(, 1).Value, 6) Like "202[0-5] C" Then
                    .Offset(0, 3).FormulaR1C1 = "=CONCAT(TEXT(RC[-1],""m/d/yyyy""),"" "",RC[-2])"
                End If
            End If
        End With
    Next Response
End Sub