Macro Compile Error

[carnut041]

I am writing a macro that will insert a formula in an empty cell that will look at the value in a different cell and based on the last character in the value, enter at different value in the formula cell. It will then copy-down, then copy and paste as values. Below is the code I have written for this:

Range("E6").Select
"=+IF(right(Range("E6"),1)=""N"",""Q"", if (right(Range("E6"),1)=""L"",""1"", if (right(Range("E6"),1)=""D"",""B"", if (right(Range("E6"),1)=""R"",""1"", if (right(Range("E6"),1)=""Q"",""B"",""2""))))))))))"
Range("E6").Select
Selection.AutoFill Destination:=Range("E6:E6512")
Range("E2:E6512").Select
Columns("E:E").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D7").Select
Application.CutCopyMode = False

When I run it I get the following error message:

COMPILE ERROR: EXPECTED: LINE NUMBER OR LABEL OR STATEMENT OR END OF STATEMENT

The line in bold-italics also highlights, so I know the issue with the syntax of that line. I am also trying to figure out the syntax for it the last character is a number. Can anyone tell me what the issue is? I am mostly self-taught, so I am sure there is a probably a better way to do this. I would appreciate any help. Thanks.

 

[JonXL]

I'd recommend using the macro recorder while entering the formula to get working code.

It will also give you the appropriate syntax to use for getting it into the cell with VBA.

 

[StephenCrump]

Assuming you're testing column E, and putting the results in column F, try:

With Range("F6:F" & Range("E" & Rows.Count).End(xlUp).Row)
    .Formula = "=IFERROR(INDEX({""B"",""1"",""Q"",""B"",""1""},MATCH(RIGHT(E6,1),{""D"",""L"",""N"",""Q"",""R""},)),""2"")"
    .Value = .Value
End With

 

[carnut041]

I'd recommend using the macro recorder while entering the formula to get working code.

It will also give you the appropriate syntax to use for getting it into the cell with VBA.

Thanks for the tip!

 

[carnut041]

Assuming you're testing column E, and putting the results in column F, try:

With Range("F6:F" & Range("E" & Rows.Count).End(xlUp).Row)
    .Formula = "=IFERROR(INDEX({""B"",""1"",""Q"",""B"",""1""},MATCH(RIGHT(E6,1),{""D"",""L"",""N"",""Q"",""R""},)),""2"")"
    .Value = .Value
End With

Thanks . Worth a shot as well, much appreciated!