VBA range.formula issue (LEN, RIGHT & LEFT)

Question

I'm trying to do a VBA code to accomplish 2 things as follows:

Count how many characters there is on cell A1, using the formula LEN(A1) and one the last line, I'm trying to have the formula RIGHT(LEFT(A1;Q1-2);6) on cell J1

Please follow down my VBA code so far:

LR = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LR
    cel = "A" & i
    cel2 = "P" & i
    cel3 = "Q" & i

    Range("P" & i).Formula = "=LEN(" & cel & ")"
    Range("J" & i).Formula = "=RIGHT(LEFT(" & cel & "," & cel3 & "-" & 2 & ")," & 6 & ")"
Next i

It seems something silly what is missing, however, I couldnt manage to solve it so far

Thanks in advance

Answer 1

You’re missing a Right, and some other things

Range("J" & i).Formula = "=RIGHT(LEFT(" & cel & "," & cel3 & "-2), 6)"