Vba vlookup

[2avrham]

Hi,

I’m using the next VLOOKUP function at excel and itworks fine à =VLOOKUP($B2,SHEET20!$1:$1048576,1,0) (im running it from different sheet (sheet1).

My problem is that when I’m record Macro with VLOOKUP formula it’s notworking.

I understood that I need to make some changes to fit VLOOKUP functionto “VBA” VLOOKUP but I failed to do it.

Any idea how can I edit this VLOOKUP to “VBA VLOOKUP”?


My code with my last try:

• I want torun VLOOKUP function on cell M2 at sheet1
  
  Sub aaa()

ActiveSheet.ListObjects("MyData").Range.AutoFilter Field:=2,Criteria1:= _
"<>"
Columns("A:B").Select
Selection.Copy
Sheets.AddAfter:=ActiveSheet
ActiveSheet.Paste

StartDate =DateAdd("d", "-7", Date)
EndDate = Now

Range("A1:b1").AutoFilter Field:=2, _
Criteria1:=">=" & StartDate, _
Operator:=xlAnd, _
Criteria2:="<=" & EndDate


Columns("A:B").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Columns("A:B").Select
Application.CutCopyMode =False
Selection.DeleteShift:=xlToLeft
Sheets("Sheet1").Select
ActiveWorkbook.SlicerCaches("Slicer_Time_filter").ClearManualFilter
ActiveWindow.ScrollColumn =2
ActiveWindow.ScrollColumn =3
ActiveWindow.ScrollColumn =4
ActiveWindow.ScrollColumn =5
ActiveWindow.ScrollColumn =6



Range("M1").Select
ActiveCell.FormulaR1C1 ="Plat"
Range("M2").Select

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
.Range("M2").Formula ="=VLOOKUP(B2,Sheet21!$A:$B,1,0)"
End With



Range("M2").Select
Selection.FillDown


End Sub

Thanks!!!

 

[offthelip]

Have a look at this thread which gives you a faster much more flexible way of doing a Vlookup in VBA without using the Vlookup function:
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[2avrham]

Hi, I failed to modify my code accordantly.. you may show me how it should be?
Thanks

 

[offthelip]

Something like this: (untested)

Sub test2()
With Worksheets("Sheet21")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 2))
End With




With Worksheets("sheet1")
lastrow2 = .Cells(Rows.Count, "B").End(xlUp).Row
search4 = Range(.Cells(1, 2), .Cells(lastrow2, 2))
' define output aray
Range(.Cells(1, 13), .Cells(lastrow2, 13)) = ""
outarr = Range(.Cells(1, 13), .Cells(lastrow2, 13))






For i = 1 To lastrow2
 For j = 1 To lastrow
  If search4(i, 1) = inarr(j, 1) Then
  outarr(i, 1) = inarr(j, 2)
  End If
 Next j
Next i


Range(.Cells(1, 13), .Cells(lastrow2, 13)) = outarr 


End With




End Sub

 

[2avrham]

Thanks but unfortunately im getting wrong results... dates and "coulmn1" data

 

[offthelip]

Can you be a bit more explicit about what you mean by "wrong results" what are you getting? and what do you want?

 

[2avrham]

That what im getting...

Column1

6/25/2018 10:05

6/25/2018 10:05

6/25/2018 10:05

6/25/2018 10:05

6/25/2018 10:05

6/25/2018 10:05

6/25/2018 10:05

6/25/2018 10:05

6/25/2018 10:05

Column1

Column1

Column1

Column1

Column1

Column1

Column1

<colgroup><col width="165" style="width: 124pt; mso-width-source: userset; mso-width-alt: 5859;"> <tbody> </tbody>

I should get:

3T449028A00899

D512A762S00636

7587820900140

MC504086A00142

88AE313100484

87LR114300496

M7GA482202462

D87361L700451

<colgroup><col width="226" style="width: 170pt; mso-width-source: userset; mso-width-alt: 8049;"> <tbody> </tbody>

it takes something else

 

[offthelip]

That still doesn't help me solve your probelm because you haven't told me where those values are coming from.
My code does the same thing as your vlookup:

[COLOR=#000000][FONT=Calibri].Range("M2").Formula ="=VLOOKUP(B2,Sheet21!$A:$B,1,0)"

is this what you want?

[/FONT][/COLOR]

 

[Fluff]

The Formula you provided is returning the same value that you are looking up, so I have made the same assumption as offthelip, in that you want to return the col B value.

Sub MyVlookup()
   Dim cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet21")
      For Each cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         Dic(cl.Value) = cl.Offset(, 1).Value
      Next cl
   End With
   With Sheets("sheet1")
      For Each cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Dic.exists(cl.Value) Then cl.Offset(, 11).Value = Dic(cl.Value)
      Next cl
   End With
End Sub

 

[2avrham]

Thanks guys, I isolate the code and both of them works fine! the only issue I have now is that im getting results of sheet 21 column B instead of A.