-2

I have a set of code that seeks out vlookups within formulas within a range of a sheet and calculates them. This worked perfectly and then one morning I came in and it didnt. Offending Code here:

sValue = Application.Evaluate(sVLOOKUP)
If Len(sValue) = 0 Then sValue = "#N/A"

for example

=VLOOKUP(Box,A1:B2,2,0)

in table

A    B
Box  1
Car  2

When manually highlighting the VLOOKUP within the in question sheet and calculating all is fine, however, this code always pulls NA because the evaluation always evaluates to ERROR 2042

My question then is what could cause the EVALUATE function to always evaluate to ERROR 2042 (NA) when the vlookup is without a doubt evaluatable?

EDIT

it would seem that the underlying issue is that I am using a form tool to reference the desired sub something to do with

Dim OutputFolder As String, GetBook As String, BookCopy As String, wb As Workbook
OutputFolder = GetFolder("C:\")
GetBook = ActiveWorkbook.Name
BookCopy = OutputFolder & "\Client Copy " & GetBook

ActiveWorkbook.SaveCopyAs BookCopy

Set wb = Workbooks.Open(BookCopy)

Dim wsDataLoad As Worksheet, wsEconomics As Worksheet, wsUI As Worksheet, wsTypeCurves As Worksheet, wsSWM As Worksheet, wsCC As Worksheet, wsSummary As Worksheet

Set wsDataLoad = wb.Sheets("DataLoad")

interaction with the formtool vs running the sub directly alters the desired output of the sub

Tyler Cowan
  • 820
  • 4
  • 13
  • 35
  • Please refine your question to be as small as possible while still producing the error - then show sample data that produces the error. – Grade 'Eh' Bacon Feb 08 '16 at 14:21
  • @Grade'Eh'Bacon Simply all VLOOKUPS evaluate to error 2042 when they don't in sheet calc i'm not sure what is so ambiguous about that? The question is Bolded.... based on documentation evaluate should work the same as pressing f9 in sheet – Tyler Cowan Feb 08 '16 at 14:29
  • I didn't say it was ambigious. What I said [well, what I implied] is that if you haven't taken the time to try and minimize your large section of code to the smallest portion which recreates the error, users of this site are less likely to try and do that for you. And showing a sample of data which recreates the error may help. – Grade 'Eh' Bacon Feb 08 '16 at 14:32
  • @Grade'Eh'Bacon as per request. – Tyler Cowan Feb 08 '16 at 14:40
  • I appreciate the effort but right now you have the statement Evaluate(sVLOOKUP) but you haven't defined sVLOOKUP. So we can't actually tell what's going on, because the code you have provided is not complete. Please see instructions here: http://stackoverflow.com/help/mcve - in short, your code should have enough info to logically flow from start to finish, while reproducing the error, and no unnecessary information. Doing this process yourself often allows *you* to debug the code yourself. – Grade 'Eh' Bacon Feb 08 '16 at 14:44
  • 1
    If your question statement is correct it won't work because there are no quotes around the string Box in your VLOOKUP statement =VLOOKUP(Box,A1:B2,2,0) – Charles Williams Feb 08 '16 at 14:50
  • yes. I believe the answer will be clear once you know what the variable `sVLOOKUP` actually is when you pass it to the `Evaluate` function. I can almost guarantee there is some syntax issue with the formula provided in `sVLOOKUP`. To see the value before you pass into the `Evaluate` function, use a `debug.print sVLOOKUP` right before the `sValue` statement. – Scott Holtzman Feb 08 '16 at 16:05
  • @ScottHoltzman I would in general agree with you scott except for I have done this and the correct value is being passed to evaluate, I even took the macro built a brand new sheet and it works as intended on the simplified case. I actually cant reproduce this error outside of the sheet. I was hoping someone might know all reasons or point to a resource that outlines all reasons that this error can be thrown so that I can go through all possible issues. Like I said in my post left a working model came back to a model that does not work with identical data. – Tyler Cowan Feb 08 '16 at 16:23
  • @TylerCowan - so you literally stepped through the code line-by-line and tested the value of `sVlookup` in your sheet before passing it to evaluate? Also, did you check to make sure nothing else in your worksheets changed - especially the ranges involved in the vlookup arguments? – Scott Holtzman Feb 08 '16 at 16:33
  • @ScottHoltzman I literally just changed nothing and It works again the only difference is instead of activating the macro via a formtool I just used the play symbol while curser in the VBA window Sub area However the form tool reference the exact same sub. if I step through using the first method I have the right values if I step through after clicking form tool I get NA – Tyler Cowan Feb 08 '16 at 16:43
  • @TylerCowan - be sure you don't have any `ActiveSheet` or `ActiveCell` statements lingering around. That could cause unexpected issues. – Scott Holtzman Feb 08 '16 at 16:46
  • @ScottHoltzman I reference the active workbook in order to A) get the file name and B) save a copy that I use to manipulate data and output a manipulated file However I dont see how using a formtool vs running sub directly from VBA window should make such a dramatic difference – Tyler Cowan Feb 08 '16 at 16:51
  • @TylerCowan - It could make a difference if the object you *think* is active is not really the active object. See [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for more. Not saying it is your issue, just bringing awareness to a possible cause. – Scott Holtzman Feb 08 '16 at 16:55
  • @ScottHoltzman Thanks Ill look into it not at all what I was expecting to be the error Oh the joys of VBA programming. – Tyler Cowan Feb 08 '16 at 16:56

1 Answers1

1

Application.Evaluate has a counter-part Worksheet.Evaluate - you should use the latter if you want to be sure your string is evaluated in the context of a specific sheet, and doesn't just default to whatever sheet is active when you run your code.

Since you've stated the string sVLOOKUP holds the correct value, this seems to be the likely explanation for what you're seeing.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125