VBA formula inside cell

Question

I need to put this formula inside a column subset range

=VLOOKUP(SUBSTITUTE(M3;"#";"");$AG$413:$AK$821;5;FALSE)

I wrote this code:

XML.Range("V3:V411").Formula = "=VLookup(Substitute(M3, ""#"", """"), $AG$413:$AK$821, 5, False)"".Value = .Value"

but doesn't work and I get "Select method of range class failed" error

Answer 1

I recommend using the R1C1 format, more stable in macros.

Also, just use the macro recorder.

Don't pass .Value to Excel, it has no idea what that means. What was the plan with that?

.FormulaR1C1 = "=VLOOKUP(SUBSTITUTE(R[2]C[12],""#"",""""),R413C33:R821C37,5,FALSE)"

Or with .Formula:

.Formula = "=VLOOKUP(SUBSTITUTE(M3,""#"",""""),$AG$413:$AK$821,5,FALSE)"