VBA Triggering IF-Statement containing Left()

Question

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.

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!

MGonet · Accepted Answer · 2024-09-18 19:47:18Z

0

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

edited Sep 18, 2024 at 19:47

answered Sep 18, 2024 at 19:41

MGonet

2,6102 gold badges3 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

by2014 Dec 17, 2024 at 15:43

Sorry for the late response, but this worked great. Thank you so much!

Scott Craner · Accepted Answer · 2024-09-18 19:16:23Z

0

"left(RC[1], 6)" is a string to vba and vba is trying to compare the two strings not the result.

And get rid of all the lines that have .Select and the whole Else block none of that is needed.

    Dim Response As Range
    For Each Response In Selection
        If Response = "Yes" Then
            If cLng(Left(Response.Offset(,2),4)) >= 2020 _
                And cLng(Left(Response.Offset(,2),4)) <= 2025 _
                And Mid(Response.Offset(,2),6,1) = "C" Then 
                Response.Offset(, 3).FormulaR1C1 = _
                        "=(CONCAT(RC[-1],"" "",RC[-2]))"
            End If
        End If

    Next Response

edited Sep 18, 2024 at 19:16

answered Sep 18, 2024 at 19:11

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Collectives™ on Stack Overflow

VBA Triggering IF-Statement containing Left()

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related