Writing Vlookups in VBA
Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Writing Vlookups in VBA

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Writing Vlookups in VBA

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Writing Vlookups in VBA

    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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,617
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Writing Vlookups in VBA

    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 by JackDanIce; May 24th, 2018 at 09:02 AM.


  4. #4
    Board Regular
    Join Date
    Mar 2017
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Writing Vlookups in VBA

    thanks both, i will work through your examples to learn

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Writing Vlookups in VBA

    @JackDanIce
    It's an R1C1 formula, so that's column1 to column3
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,617
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Writing Vlookups in VBA

    D'oh, yup missed that, thanks!


  7. #7
    Board Regular
    Join Date
    Mar 2017
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Writing Vlookups in VBA

    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?

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Writing Vlookups in VBA

    Best way to find out is to try it & see
    But do it on a copy incase
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  9. #9
    Board Regular
    Join Date
    Mar 2017
    Posts
    164
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Writing Vlookups in VBA

    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?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,296
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Writing Vlookups in VBA

    Turn these 2 lines into comments
    Code:
    .Value = .Value
                .Replace "#N/A", ""
    Is the formula entered correctly & do you get the correct values returned?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com