Vlookup alternative in vba

R

rohan85

Board Regular
Joined
Jun 27, 2013
Messages
81
hi, i have data ids in sheet1 in column a i want to lookup the ids in column a in sheet2 from a to e. what can i use apart from vlookup in vba i would appreciate your help please.

sheet1 sheet2

column a column g column a column e

123 ? 123 rr
234 ? 234 tt
567 ? 567 uu


i have the code which i got from internet but could not figure out how to plug in if you could assist please.


Code: 
Sub test() 
    Dim a, i As Long 
    a = Sheets("sheet2").Range("a1").CurrentRegion.Resize(, 2).Value 
    With CreateObject("Scripting.Dictionary") 
        For i = 2 To UBound(a, 1) 
            .Item(a(i, 1)) = a(i, 2) 
        Next 
        a = Sheets("sheet1").Range("a1").CurrentRegion.Resize(, 3).Value 
        a(1, 3) = "Supplier Name" 
        For i = 2 To UBound(a, 1) 
            If .exists(a(i, 2)) Then a(i, 3) = .Item(a(i, 2)) 
        Next 
    End With 
    Sheets("sheet1").Range("a1").CurrentRegion.Resize(, 3).Value = a 
End Sub
 
Sort by date Sort by votes
mrmmickle1 said:
Change this:

Code: 
  For Each cll In Sheets("Sheet1").Range("A2[COLOR=#ff0000][B]:A[/B][/COLOR]" & last)
Click to expand...

oops forgot that one thing sir can i use For Each cll In Sheets("Sheet1").Range("A2:Z" & last) it might happen that last used cell in column a is different then in column b is different then in column c is different and column d is different and so on.
 
Last edited:
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
In your code....these lines of code define the last row:

Code: 
   last = Worksheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlRows, _
     SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Not the line you have highlighted:
Code: 
For Each cll In Sheets("Sheet1").Range("A2:A" & last)

For example. If last = 100

Then the line you have highlighted currently loops through all cells in the range of A2:A100 (99 Cells)
If you change it to:

Code: 
  For Each cll In Sheets("Sheet1").Range("A2:Z" & last)

It will loop through all 2574 cells in that range....A2:Z100

Does that help?
 
Upvote 0
mrmmickle1 said:
In your code....these lines of code define the last row:

Code: 
   last = Worksheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlRows, _
     SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Not the line you have highlighted:
Code: 
For Each cll In Sheets("Sheet1").Range("A2:A" & last)

For example. If last = 100

Then the line you have highlighted currently loops through all cells in the range of A2:A100 (99 Cells)
If you change it to:

Code: 
  For Each cll In Sheets("Sheet1").Range("A2:Z" & last)

It will loop through all 2574 cells in that range....A2:Z100

Does that help?
Click to expand...

So sir If last = 100

and i use For Each cll In Sheets("Sheet1").Range("A2:Z" & last) it will loop through all 2574 cells in that range....A2:Z100 is that correct sir
 
Last edited:
Upvote 0
The reason iam requesting for .find method is that i would have an alternative way to do the task thanks again :)
 
Last edited:
Upvote 0
rohan85 said:
The reason iam requesting for .find method is that i would have an alternative way to do the task thanks again :)
Click to expand...



[FONT=&quot]Example - With reference to Image 1, below code shows how to use "Find Method" and "Offset Property" to do a VLookUp.[/FONT]
[FONT=&quot] [/FONT][FONT=&quot][/FONT]
findmethod_1.gif
Image 1
​
[FONT=&quot] My excel has the two tables in two different sheets and student does not have to be in class IX this criteria can be removed, could you please help sir[/FONT][FONT=&quot]
Code: 
Sub FindVLookup()[/B]
[COLOR=#008000]'using Find Method to do a VLookUp: For each student in column A, find student name in column E, and place his marks in column B, only if he is in Class IX - refer Image 1.[/COLOR]
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot] [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Dim rngSearch As Range, rngStudentNames As Range, rngFound As Range, rngStudent As Range
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot] [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Set rngSearch = ActiveSheet.Range("E3:E7")
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Set rngStudentNames = ActiveSheet.Range("A3:A7")
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot] [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot][COLOR=#008000]'searches for all student names mentioned in cells A3 to cells A7:[/COLOR]
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]For Each rngStudent In rngStudentNames
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot][COLOR=#008000]'student names are searched in Range("E3:E7"):[/COLOR]
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Set rngFound = rngSearch.Find(What:=rngStudent, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot][COLOR=#008000]'if student name is found AND if student is in class IX:[/COLOR]
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]If Not rngFound Is Nothing And rngFound.Offset(0, 1) = [COLOR=#333333]"IX"[/COLOR] Then
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot][COLOR=#008000]'found student's marks are entered in column B, against his name given in column A:[/COLOR]
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]rngStudent.Offset(0, 1) = rngFound.Offset(0, 2)
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]End If
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Next
[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot] [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot][B]End Sub
[/B][B]
[/FONT]
 
Upvote 0
Here is another example for you based on previous data example I supplied:

Code: 
[COLOR=#0000ff]Sub[/COLOR] Test()


   [COLOR=#0000ff] Dim [/COLOR]matchCll [COLOR=#0000ff]As [/COLOR]Range
 [COLOR=#0000ff]   Dim [/COLOR]lr[COLOR=#0000ff] As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] LngLp [COLOR=#0000ff]As Long[/COLOR]
    
    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row [COLOR=#008000]'Define LastRow[/COLOR]

[COLOR=#0000ff]    For [/COLOR]LngLp = 2 [COLOR=#0000ff]To[/COLOR] lr
[COLOR=#008000]        'Find Value on Sheet 2[/COLOR]
     [COLOR=#0000ff]   Set [/COLOR]matchCll = Sheets("Sheet2").Columns(1).Find(What:=Sheets("Sheet1").Cells(LngLp, 1).Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
  [COLOR=#0000ff]      If Not [/COLOR]matchCll [COLOR=#0000ff]Is Nothing Then[/COLOR]
             Sheets("Sheet1").Cells(LngLp, 2) = matchCll.Offset(, 1).Value [COLOR=#008000]'Change Accordingly[/COLOR]
[COLOR=#0000ff]        Else[/COLOR]
            MsgBox "Value Not Found"
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next[/COLOR] LngLp
    
[COLOR=#0000ff]End Sub[/COLOR]
 
Last edited:
Upvote 0
mrmmickle1 said:
Here is another example for you based on previous data example I supplied:

Code: 
[COLOR=#0000ff]Sub[/COLOR] Test()


   [COLOR=#0000ff] Dim [/COLOR]matchCll [COLOR=#0000ff]As [/COLOR]Range
 [COLOR=#0000ff]   Dim [/COLOR]lr[COLOR=#0000ff] As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] LngLp [COLOR=#0000ff]As Long[/COLOR]
    
    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row [COLOR=#008000]'Define LastRow[/COLOR]

[COLOR=#0000ff]    For [/COLOR]LngLp = 2 [COLOR=#0000ff]To[/COLOR] lr
[COLOR=#008000]        'Find Value on Sheet 2[/COLOR]
     [COLOR=#0000ff]   Set [/COLOR]matchCll = Sheets("Sheet2").Columns(1).Find(What:=Sheets("Sheet1").Cells(LngLp, 1).Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
  [COLOR=#0000ff]      If Not [/COLOR]matchCll [COLOR=#0000ff]Is Nothing Then[/COLOR]
             Sheets("Sheet1").Cells(LngLp, 2) = matchCll.Offset(, 1).Value [COLOR=#008000]'Change Accordingly[/COLOR]
[COLOR=#0000ff]        Else[/COLOR]
            MsgBox "Value Not Found"
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next[/COLOR] LngLp
    
[COLOR=#0000ff]End Sub[/COLOR]
Click to expand...

Thank you so much sir it is working fine. :)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Find, lookup, match - based on 2 separate criteria.
Replies
4
Views
200
Peter_SSs
Peter_SSs
R
  • Question
VBA Check if value in Sheet1 exists in Sheet2 then copy specific cells from Sheet2 into Sheet1
2
Replies
13
Views
758
dermie_72
D
C
  • Question
VLOOKUP 255 Character Limit - formula alternatives?
Replies
4
Views
330
Joe4
Joe4
J
  • Question
INDEX MATCH from two sheets onto third sheet?
Replies
1
Views
115
etaf
etaf
W
  • Solved
Dynamic dropdown list based on database
Replies
3
Views
204
Winnetou
W

Forum statistics

Threads
1,215,223
Messages
6,123,722
Members
449,116
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top