none
Vlookup using a dynamic range RRS feed

  • Question

  • Hello, I'm trying to re-write a macro which will find a column labled Invoice Number, which has a unique identifier which a vlookup will be run off of inserting two columns which provide a status on the invoice. The problem is the users have multiple reports with an invoice number column which could be under colum L, column J, column M..... I also need the code to run on any worksheet in the macro.

    To sum it up, how can I re-write this macro to identify the Invoice Number column, on any worksheet, regardless of where the column is located on the file, then add two new columns which the vlookup portion of the macro will populate.

    I recorded the below code which will find the invoice number but I still need code to insert the two status columns next to the Invoice Number column without naming the specific columns.

    Cells.Find(What:="Invoice   Number", After:=ActiveCell, LookIn:=xlFormulas _
              , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
              MatchCase:=False, SearchFormat:=False).Activate

    
    Private Sub OKButton_Click()
    Dim MainWkbk As Workbook
    Dim APODaily As Workbook
    'Dim result As String
    
    Set MainWkbk = ActiveWorkbook
    
    Dim Today As Date
    Today = Date
    'Dim PrevBusDay As Date
    'PrevBusDay = Date - 1
    'Select Case Weekday(PrevBusDay)
        'Case vbSunday
            'PrevBusDay = PrevBusDay - 2
        'Case vbSaturday
            'PrevBusDay = PrevBusDay - 1
    'End Select
    
    Set APODaily = Workbooks.Open("S:\3rd Party Co-broker\Invc_Status_Files\Third_Party_APO_" & Format(Today, "yyyy-mm-dd") & ".xlsx")
    
    'Display alerts code is part of original working code
    Application.DisplayAlerts = False
    MainWkbk.Activate
    Sheets(1).Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    ActiveCell.Select
    Selection.EntireColumn.Insert
    Selection.EntireColumn.Insert
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "AR Status"
    Range("L1").FormulaR1C1 = "AP Status"
    Sheets(2).Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Columns("L:M").Select
    Selection.EntireColumn.Insert
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "AR Status"
    Range("L1").FormulaR1C1 = "AP Status"
    'Display alerts code is part of original working code
    'Application.DisplayAlerts = True
    
    Dim Lookup As Worksheet
    Set Lookup = MainWkbk.Worksheets.Add
    Lookup.Name = "Lookup"
    Range("A1").Select
    
    APODaily.Activate
    ActiveSheet.Select
    If ActiveSheet.AutoFilterMode = True _
        Then ActiveSheet.AutoFilterMode = False
    Columns("U:AZ").Select
    Selection.Copy
    
    MainWkbk.Activate
    Range("A1").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Offset(1, 0).Select
      
    APODaily.Close
    'Display alerts code is part of original working code
    'Application.DisplayAlerts = False
    
    MainWkbk.Activate
    Sheets("Available for Settlement").Select
    Run_Vlookup
    Sheets("Not Available").Select
    Run_Vlookup
    
    Sheets("Lookup").Delete
    
    MsgBox ("Done")
    
    'Application display alert was false changed to true to increase speed
    Application.DisplayAlerts = True
    
    End Sub
    
    
    
    Private Sub OptionButton1_Click()
    
    End Sub
    
    Private Sub UserForm_Click()
    
    End Sub
    
    Public Function Run_Vlookup()
    ActiveSheet.Select
    'AR Status
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[2],Lookup!C1:C32,32,FALSE)"
    Range("M2").Select
    Range("M2").Copy
    Range("M2:M" & Cells.CurrentRegion.Rows.Count).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ValOut
    Columns("M:M").Select
        Cells.Replace What:="#N/A", replacement:="Closed", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    'AP Status
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[3],Lookup!C1:C32,14,FALSE)"
    Range("L2").Select
    Range("L2").Copy
    Range("L2:L" & Cells.CurrentRegion.Rows.Count).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ValOut
    Columns("L:L").Select
        Cells.Replace What:="#N/A", replacement:="Closed", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Range("A1").Select
    
    'Application display alert was part of original working code
    'Application.DisplayAlerts = False
    
    End Function
    Sub ValOut()
        Dim x, y As Integer
    ' Keyboard Shortcut: Ctrl+Shift+V
        
    On Error GoTo ErrHandler
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.SpecialCells(xlCellTypeVisible).Select
        Exit Sub
    ErrHandler:
        Application.CutCopyMode = False
        Selection.SpecialCells(xlCellTypeVisible).Select
        If Selection.Count = 1 Then
            ActiveCell.Value = ActiveCell.Value
        Else
            For Each Ecell In Selection
                Ecell.Value = Ecell.Value
            Next
        End If
    End Sub
    
    
    


    Lorac1969

    Tuesday, March 1, 2016 7:50 PM

Answers

  • >>>I recorded the below code which will find the invoice number but I still need code to insert the two status columns next to the Invoice Number column without naming the specific columns.

    According to your description, please correct me if I have any misunderstandings on your question, you could refer to codes below:

    Sub DemoFind()
        
        With ActiveSheet.UsedRange
          Set cell = .Find(What:="Invoice Number", LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
    
        If Not cell Is Nothing Then
            
            firstAddress = cell.Address
            Do
    '            Debug.Print cell.Address
    '            Debug.Print cell.Column
                ActiveSheet.Columns(cell.Column + 1).Insert
                ActiveSheet.Columns(cell.Column + 1).Insert
                Set cell = .FindNext(cell)
            Loop While Not cell Is Nothing And cell.Address <> firstAddress
    
        End If
        End With
    
    End Sub
    

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:08 PM
    Wednesday, March 2, 2016 6:37 AM

All replies

  • >>>I recorded the below code which will find the invoice number but I still need code to insert the two status columns next to the Invoice Number column without naming the specific columns.

    According to your description, please correct me if I have any misunderstandings on your question, you could refer to codes below:

    Sub DemoFind()
        
        With ActiveSheet.UsedRange
          Set cell = .Find(What:="Invoice Number", LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
    
        If Not cell Is Nothing Then
            
            firstAddress = cell.Address
            Do
    '            Debug.Print cell.Address
    '            Debug.Print cell.Column
                ActiveSheet.Columns(cell.Column + 1).Insert
                ActiveSheet.Columns(cell.Column + 1).Insert
                Set cell = .FindNext(cell)
            Loop While Not cell Is Nothing And cell.Address <> firstAddress
    
        End If
        End With
    
    End Sub
    

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:08 PM
    Wednesday, March 2, 2016 6:37 AM
  • Thanks a bunch, that did the trick.

    Lorac1969

    Friday, March 4, 2016 7:58 PM
  • Hi, Lorac1969

    You are welcome, we are glad to help you resolve your issue.

    Monday, March 7, 2016 9:36 AM