• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autolookup based on only numbers

Hello All,

Please be informed that i need to get autolookup with out using formula and click on macro button.

I want get value from Sheet4 columnB to Sheet2 ColumnH, based on range Sheet2 ColumnG and sheet4 ColumnA:ColumnB

below code was working for text values, but for numbers it is not working.

>>> use code - tags <<<
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColumnHCells As Range
Set ColumnHCells = Intersect(Columns(7), Target)
If Not ColumnHCells Is Nothing Then
With ActiveSheet
  For Each cll In ColumnHCells.Cells
    zzz = Evaluate("WorksheetFunction.VLOOKUP(""" & cll.Value & """,Sheet4!$A$1:$B$3,2,FALSE)")
    If Not IsError(zzz) Then cll.Offset(, 4) = zzz
  Next cll
  End With
  End If
End Sub
Thank you in advance



Last edited by a moderator:

Marc L

Excel Ninja
as above a codeline has no nense, doubt can ever work even with text values,​
as you forgot to attach the workbook containing the well working original VBA procedure …​

Marc L

Excel Ninja
but for numbers it is not working
'Cause Sheet2 column G does not contain number but texts only according to the cells formatting …​
Once columns with the same cells formatting this VBA event procedure works as expected :​
Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rg As Range, Rc As Range
        Set Rg = Intersect(UsedRange.Columns(7), Target):  If Rg Is Nothing Then Exit Sub
    For Each Rc In Rg
        Rc(1, 2) = Application.IfError(Application.VLookup(Rc, Sheet2.[A1].CurrentRegion, 2, False), "")
        Set Rg = Nothing
End Sub
Do you like it ? So thanks to click on bottom right Like !​