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"
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?
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
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