Hi All,

I am having trouble converting a VLOOKup formula into VBA Code.

The excel code that I have working is as follows that works in excel:

=VLOOKUP(LEFT(B2,5)&"*",'Co-has CCN'!$B$2:$C$2965,2,FALSE)

I want this to be coded in VBA so that we can have the formula paste down to the last row.

I have tried to do something like this in VBA:

Sub test()

Worksheets("Co-missing CCN").Select

With Worksheets("Co-missing CCN")

.Range("C2:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).Formula = "=VLOOKUP(LEFT(B2,5)&" * ",'Co-has CCN'!$B$2:$C$2965,2,FALSE)"

End With

End Sub

However, I get a runtime error 13 on the line above. Can anyone please help?

Thanks