none
Excel VBA code for running a vlookup against a dynamic range RRS feed

  • Question

  • Hello,

    I created a macro which allows users to confirm the invoice status on report A. The users liked it so much they'd like to be able to use it on all reports, all of the reports would have an Invoice Number column but it could be in any column. How do I create a dynamic range to run the vlookup on any report with an Invoice Number column? Below is part of the original code.

    Public Function Run_Vlookup()

    ActiveSheet.Select

    'AR Status

    Range("M2").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[2],Lookup!C1:C4,4,FALSE)"

    Range("M2").Select

    Range("M2").Copy

    Range("M2:M" & Cells.CurrentRegion.Rows.Count).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Columns("M:M").Select

    Application.Run "ValOut"

    Columns("M:M").Select

        Cells.Replace what:="#N/A", replacement:="", lookat:=xlPart, _

            searchorder:=xlByColumns, MatchCase:=False, searchformat:=False, _

            ReplaceFormat:=False

    'AP Status

    Range("L2").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[3],Lookup!C1:C1,2,FALSE)"

    Range("L2").Select

    Range("L2").Copy

    Range("L2:L" & Cells.CurrentRegion.Rows.Count).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Columns("L:L").Select

    Application.Run "ValOut"

    Columns("L:L").Select

        Cells.Replace what:="#REF!", replacement:="Open", lookat:=xlPart, _

            searchorder:=xlByColumns, MatchCase:=False, searchformat:=False, _

            ReplaceFormat:=False

    Columns("L:L").Select

        Cells.Replace what:="#N/A", replacement:="Closed", lookat:=xlPart, _

            searchorder:=xlByColumns, MatchCase:=False, searchformat:=False, _

            ReplaceFormat:=False

    Columns("L:M").Select

    Application.Run "ValOut"

    Range("A1").Select


    Lorac1969

    Monday, August 24, 2015 12:44 PM

Answers

  • How do I create a dynamic range to run the vlookup on any report with an Invoice Number column? Below is part of the original code.

    At first, when you ask such question, don't post only a part, post the whole macro and used sub macros too. That makes it more easier for us to understand your macro.

    And don't post it as normal text, use the code block editor. In this forum above the editor when you write a post there is an icon with a "<>" sign, tool tip "Insert Code Block". Click that icon and select VB.NET as language.

    At second, I have to show you how to make such macros more faster and easier more readable, this macro does the (nearly) same as your macro (a little bit optimized). Please check the formula in column L, I'm not sure if you really use this formula:

    Sub Run_Vlookup_AK()
      Dim Ws As Worksheet
      Dim Source As Range, Dest As Range
      
      Set Ws = ActiveSheet
      
      'Access this sheet
      With Ws
        'Get the used cells in column A
        Set Source = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
        'Map to column M
        Set Dest = Intersect(Source.EntireRow, .Range("M:M"))
        With Dest
          'Create the formula
          .Formula = "=VLOOKUP(O2,Lookup!$A:$D,4,FALSE)"
          'Convert to values
          .Value = .Value
          'Remove errors
          .Replace "#N/A", ""
        End With
        'Map to column L
        Set Dest = Intersect(Source.EntireRow, .Range("L:L"))
        With Dest
          'Create the formula
          .Formula = "=VLOOKUP(O2,Lookup!$A:$A,2,FALSE)"
          'Convert to values
          .Value = .Value
          'Remove errors
          .Replace "#REF!", "Open"
          .Replace "#N/A", "Closed"
        End With
      End With
    End Sub

    To run such a macro over a bunch (or all) sheets is very easy, you can move the worksheet variable "Ws" into the header of the sub and so "put a worksheet to the sub" as argument from the outside:

    Sub Run_Vlookup_AK_Arg(ByVal Ws As Worksheet)
      Dim Source As Range, Dest As Range
      
      'Access this sheet
      With Ws
      
      'Etc. rest of the code as above

    And here is a main sub to show how this sub can be called:

    Sub Main()
      Dim Ws As Worksheet
      'On all worksheet
      For Each Ws In Worksheets
        'If the name begins with "Report"
        If InStr(1, Ws.Name, "Report", vbTextCompare) = 1 Then
          Run_Vlookup_AK_Arg Ws
        End If
      Next
    End Sub

    Any questions?

    Andreas.

    Monday, August 24, 2015 3:29 PM
  • You can create a formula in code like this, which requires 4 range selections (one for the formula, one for the index, on for the table, and one for the return from the table).

    Sub Run_Vlookup()
        
        Dim rFrm As Range
        Dim rVal As Range
        Dim rTable As Range
        Dim rRet As Range
        
        Set rFrm = Application.InputBox("Select the cell for the formula", Type:=8)
        Set rVal = Application.InputBox("Select the cell with the first Invoice Number", Type:=8)
        Set rTable = Application.InputBox("Select the entire table with the Invoice Data", Type:=8)
        Set rRet = Application.InputBox("Select a cell with the Data return", Type:=8)
        
        With rFrm.Resize(rFrm.CurrentRegion.Cells(rFrm.CurrentRegion.Cells.Count).Row - rFrm.Row + 1)
            .Formula = "=VLOOKUP(" & rVal.Address(False, False) & "," & rTable.Address(True, True, xlA1, True) & "," & rRet.Column - rTable.Cells(1).Column + 1 & ",FALSE)"
            .Value = .Value
            rFrm.Parent.Activate
            rFrm.EntireColumn.Select
            Application.Run "ValOut"
            
            On Error Resume Next
            .SpecialCells(xlCellTypeConstants, 16).Clear
        End With
        
    End Sub


    Monday, August 24, 2015 3:45 PM

All replies

  • How do I create a dynamic range to run the vlookup on any report with an Invoice Number column? Below is part of the original code.

    At first, when you ask such question, don't post only a part, post the whole macro and used sub macros too. That makes it more easier for us to understand your macro.

    And don't post it as normal text, use the code block editor. In this forum above the editor when you write a post there is an icon with a "<>" sign, tool tip "Insert Code Block". Click that icon and select VB.NET as language.

    At second, I have to show you how to make such macros more faster and easier more readable, this macro does the (nearly) same as your macro (a little bit optimized). Please check the formula in column L, I'm not sure if you really use this formula:

    Sub Run_Vlookup_AK()
      Dim Ws As Worksheet
      Dim Source As Range, Dest As Range
      
      Set Ws = ActiveSheet
      
      'Access this sheet
      With Ws
        'Get the used cells in column A
        Set Source = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
        'Map to column M
        Set Dest = Intersect(Source.EntireRow, .Range("M:M"))
        With Dest
          'Create the formula
          .Formula = "=VLOOKUP(O2,Lookup!$A:$D,4,FALSE)"
          'Convert to values
          .Value = .Value
          'Remove errors
          .Replace "#N/A", ""
        End With
        'Map to column L
        Set Dest = Intersect(Source.EntireRow, .Range("L:L"))
        With Dest
          'Create the formula
          .Formula = "=VLOOKUP(O2,Lookup!$A:$A,2,FALSE)"
          'Convert to values
          .Value = .Value
          'Remove errors
          .Replace "#REF!", "Open"
          .Replace "#N/A", "Closed"
        End With
      End With
    End Sub

    To run such a macro over a bunch (or all) sheets is very easy, you can move the worksheet variable "Ws" into the header of the sub and so "put a worksheet to the sub" as argument from the outside:

    Sub Run_Vlookup_AK_Arg(ByVal Ws As Worksheet)
      Dim Source As Range, Dest As Range
      
      'Access this sheet
      With Ws
      
      'Etc. rest of the code as above

    And here is a main sub to show how this sub can be called:

    Sub Main()
      Dim Ws As Worksheet
      'On all worksheet
      For Each Ws In Worksheets
        'If the name begins with "Report"
        If InStr(1, Ws.Name, "Report", vbTextCompare) = 1 Then
          Run_Vlookup_AK_Arg Ws
        End If
      Next
    End Sub

    Any questions?

    Andreas.

    Monday, August 24, 2015 3:29 PM
  • You can create a formula in code like this, which requires 4 range selections (one for the formula, one for the index, on for the table, and one for the return from the table).

    Sub Run_Vlookup()
        
        Dim rFrm As Range
        Dim rVal As Range
        Dim rTable As Range
        Dim rRet As Range
        
        Set rFrm = Application.InputBox("Select the cell for the formula", Type:=8)
        Set rVal = Application.InputBox("Select the cell with the first Invoice Number", Type:=8)
        Set rTable = Application.InputBox("Select the entire table with the Invoice Data", Type:=8)
        Set rRet = Application.InputBox("Select a cell with the Data return", Type:=8)
        
        With rFrm.Resize(rFrm.CurrentRegion.Cells(rFrm.CurrentRegion.Cells.Count).Row - rFrm.Row + 1)
            .Formula = "=VLOOKUP(" & rVal.Address(False, False) & "," & rTable.Address(True, True, xlA1, True) & "," & rRet.Column - rTable.Cells(1).Column + 1 & ",FALSE)"
            .Value = .Value
            rFrm.Parent.Activate
            rFrm.EntireColumn.Select
            Application.Run "ValOut"
            
            On Error Resume Next
            .SpecialCells(xlCellTypeConstants, 16).Clear
        End With
        
    End Sub


    Monday, August 24, 2015 3:45 PM