"=IFERROR(VLOOKUP(B2,'sheet'!B:M,12,FALSE),MID(B2, FIND(CHAR(1),SUBSTITUTE(B2," - ",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(B2," - ",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(B2," - ",CHAR(1),2))-4)*0.025)"
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Enter Formulas in Excel with a Macro
0
Hi tried adding vlookup formula in excel with Macro, following your video, Excel Macro VBA Tip 9 - Enter Formulas in Excel with a Macro, I entered the following formula into the quotes but when I tried running, it gave me the error "Run-time erro '13': Type mismatch" can you please help?
Answers
0
Hi Sugarpaste and welcome to the Forum.
I don't know which of Don's tutorial's you are referring to but I see two problems in the formula you're attemping to add (which I can't check without your file!).
Firstly, I don't know why your formula string contains 'sheet' (since Excel uses sheet! or Sheet2! as I use below). Secondly, your formula contains three instances of " _ " - that has the effect of cutting the string into several bits separated by the underscore _ , like "<1>" _ "<2>" _ "<3>" _ "<4>" _ "<5>" which confuses VBA! A workaround for that is to the replaced each " _ " with " & Chr(34) & " _ " & Chr(34) & " where Chr(34) gives the " (34 being the ASCII character number), so the sub-strings are joined together in a singke string eith with the &s.
This sub first sets a string string variable FormString to the full formula (with Sheet2! note plus the " workaround) then applies that to the active cell:
Sub AddFormula()
FormString = "=IFERROR(VLOOKUP(B2,Sheet2!B:M,12,FALSE),MID(B2, FIND(CHAR(1),SUBSTITUTE(B2," & Chr(34) & " - " & Chr(34) & ",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(B2," & Chr(34) & " - " & Chr(34) & ",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(B2," & Chr(34) & " - " & Chr(34) & ",CHAR(1),2))-4)*0.025)"
ActiveCell.Formula = FormString
End Sub