Writing Vlookups in VBA

Godders199

Active Member
Joined
Mar 2, 2017
Messages
313
Office Version
  1. 2013
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
One option
Code:
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
 
Upvote 0
Couple of suggestions:
Code:
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
Code:
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?)
 
Last edited:
Upvote 0
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?
 
Upvote 0
Best way to find out is to try it & see ;)
But do it on a copy incase
 
Upvote 0
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?
 
Upvote 0
Turn these 2 lines into comments
Code:
.Value = .Value
            .Replace "#N/A", ""
Is the formula entered correctly & do you get the correct values returned?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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