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!!!

 

[Fluff]

Are you trying to lookup the value from col B on sheet1, in col A on sheet21 & if found return the value from col B on sheet21?

 

[2avrham]

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

I want to return Sheet 21 A column results ( now im getting column B)

 

[Fluff]

So you want to return the same value that you looked up?

 

[2avrham]

I changed the offset to "0" and it works great!!! thanks a lot!!!!

 

[2avrham]

Yes... I changed the offset and it works great! Thanks a lot!!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[offthelip]

with my code all you need to do to pick up column A is change this line:

outarr(i, 1) = inarr(j, 2)

to

outarr(i, 1) = inarr(j, 1)

However I do wonder why you were trying to do this with Vllookup in the first place when it appears that all you were trying to do was check to see if one variable existed in another list. For information you can do this quite easily with the MATCH function or the COUNTIF function.