Writing Vlookups in VBA

[Godders199]

Hello, I am always recording and adding vlookup into Marco's as below, I will like to understand how to write it within vba rather than recording everything, also if possible how only to put the result in the cell , rather than the formula.

the basic formula is

=IFERROR(VLOOKUP(AB2,'SQ Hierarchy'!A:C,3,FALSE),"")

My current code when i add to by vba is

Dim LR1 As Long
LR1 = Range("a" & Rows.Count).End(xlUp).Row
Range(“z2”).select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[2],'SQ Hierarchy'!C[-25]:C[-23],3,FALSE),"""")"
Range("Z3").Select
Range("z2").Copy
Range("z3:z" & LR1).PasteSpecial xlPasteAll
Application.CutCopyMode = False


This is just one example, of the 10 I need to add in, I am just looking to progress my knowledge and make my VBA look more professional

I have tried working through some examples i have found online but they never seem to work when adapting to my worksheets.

Any help would be appreciated

 

[Fluff]

One option

Dim LR1 As Long
LR1 = Range("a" & Rows.Count).End(xlUp).Row
With Range("Z2:Z" & LR1)
   .FormulaR1C1 = _
      "=IFERROR(VLOOKUP(RC[2],'SQ Hierarchy'!C1:C3,3,FALSE),"""")"
   .Value = .Value
End With

 

[JackDanIce]

Couple of suggestions:

Sub Form_VLOOKUP()

    Dim x   As Long
    Dim LR  As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("SQ Hierarchy")
        x = .Cells(.Rows.Count, 3).End(xlUp).Row
    End With
    
    With ActiveSheet
        LR = .Cells(.Rows.Count, 28).End(xlUp).Row - 1
        With .Cells(2, 26).Resize(LR)
            .Formula = "=VLOOKUP(AB2,'SQ Hierarchy'!$A$1:$C$" & x & ",3,0)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
    End With
    
    Application.ScreenUpdating = True
    
End Sub

And

Sub Dic_VLOOKUP()
    
    Dim x       As Long
    Dim arr()   As Variant
    Dim dic     As Object
    
    Set dic = CreateObject("Scripting.Dictionary")

    With Sheets("SQ Hierarchy")
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr = .Cells(2, 1).Resize(x, 3).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 3)
    Next x
    
    With ActiveSheet
        x = .Cells(.Rows.Count, 28).End(xlUp).Row
        arr = .Cells(2, 28).Resize(x).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            arr(x, 1) = IIf(dic.exists(arr(x, 1)), dic(arr(x, 1)), vbNullString)
        Next x
        .Cells(2, 26).Resize(UBound(arr, 1)).Value = arr
    End With
    
    Erase arr
    Set dic = Nothing
    
End Sub

(@Fluff, think your formula needs to change C1 to A1?)

 

[Godders199]

thanks both, i will work through your examples to learn

 

[Fluff]

@JackDanIce
It's an R1C1 formula, so that's column1 to column3

 

[JackDanIce]

D'oh, yup missed that, thanks!

 

[Godders199]

thanks i have worked through and understand the principles , and can use, one final question, if the user filters or resorts the data do the lookups remain correct , or do i need to protect the workbook?

 

[Fluff]

Best way to find out is to try it & see
But do it on a copy incase

 

[Godders199]

Sorry, just practising this , this is my basic formula i am looking at

=IFERROR(VLOOKUP(A2,'Check Summary'!A:C,3,FALSE),"")
this is the code i think should work

Dim x As Long
Dim LR As Long

With Sheets("checks")
x = .Cells(.Rows.Count, 3).End(xlUp).Row - "used column 3 as no blank cells"
End With

With ActiveSheet
LR = .Cells(.Rows.Count, 25).End(xlUp).Row - 1 - "used column 25 as no blank cells"
With .Cells(2, 8).Resize(LR)
.Formula = "=VLOOKUP(a2,'checks'!$A$1:$C$" & x & ",3,0)"
.Value = .Value
.Replace "#N/A", ""
End With
End with

but i get no results shown, am I being stupid or have i done something wrong?

 

[Fluff]

Turn these 2 lines into comments

.Value = .Value
            .Replace "#N/A", ""

Is the formula entered correctly & do you get the correct values returned?