VBA Code to get a formula returned in column2 when column1 is not empty, error in my formula

Question

I have the following VBA code, but one line is giving an error:

rng(i, 4).Formula = "=IF(LEFT(A" & (i + 1) & ",2)="RN","RN",IF(LEFT(A" & (i + 1) & ",2)="EX","EX",""))"

Can someone help me correct this?

Sub ITEM()
    Dim dat As Variant
    Dim rng As Range
    Dim i As Long

    Set rng = Range("F5:F50000")
    dat = rng
    For i = LBound(dat, 1) To UBound(dat, 1)
        If dat(i, 1) <> "" Then
            rng(i, 2).Formula = "=(F" & (i + 1)
            rng(i, 3).Formula = "=vlookup(J" & (i + 1) & ",A:B,2,0)"
            rng(i, 4).Formula = "=IF(LEFT(A" & (i + 1) & ",2)="RN","RN",IF(LEFT(A" & (i + 1) & ",2)="EX","EX",""))"
            
        End If
    Next
End Sub

In VBA, don't quotes inside a string need to be doubled? So rng(i, 4).Formula = "=IF(LEFT(A" & (i + 1) & ",2)=""RN"",""RN"",IF(LEFT(A" & (i + 1) & ",2)=""EX"",""EX"",""""))" — ninadepina
– ninadepina, Commented Nov 21 at 10:21
...or without the repetition of LEFT() (assuming newish Excel version) "=LET(lft,LEFT(A" & (i + 1) & ",2),switch(lft,""RN"",lft,""EX"",lft,""""))" — Tim Williams
– Tim Williams, Commented Nov 21 at 17:34

paxdiablo · Accepted Answer · 2025-11-21 10:40:18Z

Your first formula appears to have mismatched parentheses, it should be one of:

rng(i, 2).Formula = "=(F" & (i + 1) & ")"
rng(i, 2).Formula = "=F" & (i + 1)

The problem there is that you put the wrong thing in the field but the VBA instruction itself is valid.

The next one looks to be a genuine syntax error that would result in VBA not even being able to interpret the instruction.

Specifically, the third formula looks like you're wanting to put literal double quotes within the string, such as with "RN". If this is the case, you'll need to escape them, by using double double quotes.

For example, the code:

xxx = "My user name is ""paxdiablo"""

would result in the string My user name is "paxdiablo".

Your desired line in that case would be something like (doubling each double quote within the string segments, i.e., those not used for inserting i+1):

rng(i, 4).Formula = "=IF(LEFT(A" & (i + 1) & ",2)=""RN"",""RN"",IF(LEFT(A" & (i + 1) & ",2)=""EX"",""EX"",""""))"

Collectives™ on Stack Overflow

VBA Code to get a formula returned in column2 when column1 is not empty, error in my formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related