Autolookup based on only numbers

[Anantha Krishna]

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

Regards,
Anantha

 

[Marc L]

Hello,​

​

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 …​

​

 

[Anantha Krishna]

attached file :

 

[Marc L]

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), "")
    Next
        Set Rg = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​