How to use evaluate()

[Nitesh Khot]

Hi..

I have data in sheet1 and data range is

A2:D4000

and I want to use vlookup in evaluate () into sheet2 in range "B2:B500" then if vlookup value not found then give me error msg otherwise msg okie..

 

[Luke M]

If VB speak, syntax would be like:

Sub test()
Dim x As Variant

x = Evaluate("=VLOOKUP(A2,D2:E4,2,FALSE)")

MsgBox x
End Sub

If you're just want to know if a value is found though, a better way would be:

Sub IsItThere()
Dim fCell As Range
Dim findThis As String
Dim rngSearchHere As Range

'What are you looking for?
findThis = "a"

'Where are we looking?

Set rngSearchHere = Range("D:D")

Set fCell = rngSearchHere.Find(findThis)

If fCell Is Nothing Then
    MsgBox "Value not found"
Else
    MsgBox "I found it!"
End If

End Sub

 

[Nitesh Khot]

Thank you for reply and valuable solutions...

But How Can I use vlookup result in array i.e

same like as below...

str = "=TRANSPOSE(IF(((COUNTIF($H$2:$H$" & r & ",$H$2:$H$" & r & ")=1)*COUNTIF($G$2:$G$" & _
r & ",$G$2:$G$" & r & ")=1),FALSE,ADDRESS(ROW($H$2:$H$" & r & "),2)))"

evaluate (str)

 

[Nitesh Khot]

Hi Luke Sir..,

I am unable to find out two error value using below code ....Please can you check

Sub vlooupcheck()
Dim lastrow As String

Dim fCell As Range
Dim fCell1 As Range
Dim findThis As String
Dim findThis1 As String
Dim rngSearchHere As Range
findThis = "#N/A"
findThis1 = "#VALUE!"
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Set rngSearchHere = Range("P2:Q" & lastrow)
   
 Set fCell = rngSearchHere.Find(findThis)
Set fCell1 = rngSearchHere.Find(findThis1)

If fCell Is Nothing or fCell1 Is Nothing Then
Call pivot_check
Else
        fCell.Font.Color = vbRed
        fCell1.Font.Color = vbRed
     MsgBox "Something Went Wrong..! Please check Col P & Q"

     Exit Sub

end sub

 

[Marc L]

Hi !​

If VB speak, syntax would be like:

Sub test()
Dim x As Variant

x = Evaluate("=VLOOKUP(A2,D2:E4,2,FALSE)")

MsgBox x
End Sub

Objection your honnor ‼

This code works only for active worksheet, TBTO rule …
If data are in Sheet1 but Sheet2 is active, like this can't work !
To work in any case, must reference worksheet in code like
x = Sheet1.Evaluate("VLOOKUP(A2,$D$2:$E$4,2,FALSE)") …

Here Sheet1 is the default CodeName (see in VBA inner help)
of first worksheet usually name Sheet1 …

As a sample, let' say Sheet1 is named Data1 and Sheet2 is Data2.
In Luke' sample, imagine A2 is in Sheet2 and $D$2:$E$4 in Sheet1,
my codeline above fails if Sheet2 is not active !

In this case whatever is the active worksheet :

• x = Sheet1.Evaluate("VLOOKUP(Data2!A2,$D$2:$E$4,2,FALSE)")

• x = Sheet2.Evaluate("VLOOKUP(A2,Data1!$D$2:$E$4,2,FALSE)")

• x = Evaluate("VLOOKUP(Data2!A2,Data1!$D$2:$E$4,2,FALSE)") …
​

 

[Luke M]

Hi Nitesh,

Based on your last macro, it looks like you just want to know if an error is in the results?

Sub vlookupCheck()

Dim rngBadCells As Range
Dim lastRow As Long
lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

'Check for errors
On Error Resume Next
Set rngBadCells = Range("P2:Q" & lastRow).SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If rngBadCells Is Nothing Then
    Call pivot_check
Else
    rngBadCells.Font.Color = vbRed
    MsgBox "Something Went Wrong..! Please check Col P & Q"
End If

End Sub

 

[Nitesh Khot]

Dear Luke Sir..


Its working only when activecell having formula with error....But How to find if we paste formula as value in cell & then run code ......


I want to find out value like #N/A , #NA, #VALUE!, #DIV0!

 

[Luke M]

If the cells you are checking are constants, rather than formulas, then change this line

Set rngBadCells = Range("P2:Q" & lastRow).SpecialCells(xlCellTypeFormulas, xlErrors)

to this

Set rngBadCells = Range("P2:Q" & lastRow).SpecialCells(xlCellTypeConstants, xlErrors)

 

[Nitesh Khot]

Awesome.......very nice..

Luke Sir..

it is possible to store vlookup result in array()
i.e If my Vlookup execute in range (A1:A100)
then
I want A1:A100 result into array().

How can we do this VBA..

 

[Luke M]

Simple example:

Sub test()
dim x as Variant
x = Range("A1:A100").Value

MsgBox x(1, 1)
End Sub

 

[Nitesh Khot]

Thanks for replying...

Sir ..,I want to use Vlookup function inside Evaluate()

for e.g
x = Evaluate("VLOOKUP($A$2:$A$10,Data1!$D$2:$E$4,2,FALSE)")

After running this function the result of A2:A10 will be stored like

x=("Nikh","Luke","Chandoo","#N/A","Deepak")

After that I want to check If any error like "#N/A" is available inside this result if available then show msgbox else exit sub...

 

[Luke M]

Hi Nitesh,

I think you're mixing up which step you're on, and which arguments you have. The VLOOKUP function's first argument should be a single value, not a range. Second, the formula can't be in A2 if it's referencing A2.

How about you take a step back, and re-state what the overall goal is? What is currently in A2:A10? If you focus on where you are now, and where you want to get to, we can help find the best path. Right now I'm not sure why you keep wanting to use the EVALUATE method.

 

[Nitesh Khot]

Hi Sir....

I know I am not explaining better to understand but pls find attached file for what I want...


I want to use evaluate function because I dont want to paste vlookup in sheet..due to this if you have huge data then file gets Not Responding...If we use Evaluate function then there is not matter of vlookup paste in sheet and we can easily find if it is possible to store vlookup result in array("value",#N/A") like this

 

[Luke M]

You will not gain any calculation speed improvement by storing the value into a VB array over just writing to the cells. If speed is really suffering, I would suggest sorting the source table (for the VLOOKUP), so that the formula doesn't have to work as hard.

Sub FindErrors()
Dim ws As Worksheet
Dim lastRow As Long
Dim rngBad As Range

'which sheet are we working with?
Set ws = Worksheets("Sheet1")

Application.ScreenUpdating = False
With ws
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   
    With .Range("B2:B" & lastRow)
        'Write the formula
        .Formula = "=VLOOKUP(A2,Sheet2!$A$2:$B$7,2,0)"
   
        'Store as values
        .Copy
        .PasteSpecial xlPasteValues
       
        'Check for errors
        On Error Resume Next
        Set rngBad = .SpecialCells(xlCellTypeConstants, xlErrors)
        On Error GoTo 0
       
        'Check test
        If Not rngBad Is Nothing Then
            MsgBox "errors found"
            Application.Goto rngBad
        End If
    End With
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
   
End Sub