CODEWITHSUNDEEP
excelvba
Vic13
Vic13

Reputation: 561

Using vlookup function with VBA

x = Application.GetOpenFilename(Title:="Please Select the required File")

lNewBracketLocation = InStrRev(x, Application.PathSeparator)

x = Left$(x, lNewBracketLocation) & "[" & Right$(x, Len(x) - lNewBracketLocation)

Sheets("T0").Range("AC2").FormulaR1C1 = "=IFERROR(VLOOKUP(LEFT(RC[-28],8),'" & x & "]SheetName'!C1:C3,3,0,)""Not Available"")"

I am asking the user to select the file which the user needs to perform the mapping task and for that I am using the above code, but it isn't working.

Upvotes: 0

Views: 56

Answers (1)

FunThomas
FunThomas

Reputation: 29146

The error comes from a syntax error in your formula. So Excel (not VBA) complains about that error by raising an "application-defined" error to VBA, and VBA shows it to you. You simply cannot enter an invalid formula into a cell. If you try manually from Excel, you will get something like "There is an error with that formula".

Now, while it's hard to write a complicated formula in Excel, it's even harder to get it right from VBA. Following things can help to find the problem:

(1) Don't write the formula direct into the excel range. Use an intermediate variable:

 Dim formula as string
 formula = "=IFERROR(VLOOKUP(LEFT(RC[-28],8),'" & x & "]SheetName'!C1:C3,3,0,)""Not Available"")"
 Debug.Print formula
 Sheets("T0").Range("AC2").FormulaR1C1 = formula

(2) Write the formula into Excel manually, keep the cell active, switch to the VBA environment and type in the immediate window:

? activecell.FormulaR1C1

Compare the result with the content of the formula-variable. If they are not identically, you probably have found your problem.

In your case, you have a misaligned comma in the formula. Look at C1:C3,3,0,) - it should have the closing parenthesis before the comma: C1:C3,3,0),

"=IFERROR(VLOOKUP(LEFT(RC[-28],8),'" & x & "]SheetName'!C1:C3,3,0),""Not Available"")"

I cannot guarantee that this the only error in the formula - but I hope it can help to narrow down such problems.

Upvotes: 2

Related Questions