Using vlookup function with VBA

Question

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.

Could you elaborate what *"it isn't working" means? Also, when writing a formula with VBA, it is a good idea to first write the formula into a variable and, if something fails, check the content of the variable (use the immediate window). — FunThomas
– FunThomas, Commented Jul 29, 2020 at 14:53
@FunThomas I am getting application-defined or object-defined error. I am new to VBA so don't know how to resolve it. — Vic13
– Vic13, Commented Jul 29, 2020 at 14:56
Debug.Print x to see that value as you test. Also try assigning a simple value like 3 to the sheet formula. We can't tell if that sheet reference is valid. — HackSlash
– HackSlash, Commented Jul 29, 2020 at 15:17

FunThomas · Accepted Answer · 2020-07-29 15:21:57Z

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.

Collectives™ on Stack Overflow

Using vlookup function with VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related