none
Excel 2010 VBA code for finding a column then naming the column as a range to use the named range in a vlookup RRS feed

  • Question

  • Hello,

    I've been attempting to write a macro which users will run against a file they sent to their customers. The goal is to find the current AR and AP amount of each invoice based off of the unique identifier labeled as payable ID. Sometimes the customers may delete a column or insert one, so the payable ID column may move. I'm trying to write a macro to find the payable ID column, then name it as a range to run a vlookup against the named range. Below is the code I have so far. Any suggestions? The current code isn't naming the column as a range, it names the entire sheet.

    Private Sub OKButton_Click()
    
    Dim MainWkbk As Workbook
    Dim APODaily As Workbook
    Dim Detail As Worksheet
    
    Set MainWkbk = ActiveWorkbook
    Set Detail = MainWkbk.ActiveSheet
    
    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(PrevBusDay, "yyyy-mm-dd") & ".xlsx")
    
    Application.DisplayAlerts = False
    MainWkbk.Activate
    ActiveSheet.Name = "Detail"
    Sheets(1).Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    'Name range code not working
    'Columns.Find("Payables ID").Cells.CurrentRegion.Select
    
    Cells.Find(What:="Payable", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.UsedRange.Rows.Count.Select
    ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=Selection
    'NameRange
    Columns("M:M").Select
    Selection.EntireColumn.Insert
    Selection.EntireColumn.Insert
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Current AR Remaining"
    Range("N1").FormulaR1C1 = "Current AP Remaining"
    Columns("M:N").NumberFormat = "General"
    'Display alerts code is part of original working code
    'Application.DisplayAlerts = True
    
    MainWkbk.Worksheets.Add(After:=Worksheets(1)).Name = "Lookup"
    Range("A1").Select
    
    APODaily.Activate
    ActiveSheet.Select
    If ActiveSheet.AutoFilterMode = True _
        Then ActiveSheet.AutoFilterMode = False
    Columns("AD:BC").Select
    Selection.Copy
    
    MainWkbk.Activate
    Range("A1").Select
    ActiveSheet.Paste
    Columns("B:U").Delete
    Columns("A:A").Copy
    Columns("G:G").Select
    ActiveSheet.Paste
    Detail.Activate
    
    APODaily.Close
    'Display alerts code is part of original working code
    'Application.DisplayAlerts = False
    
    MainWkbk.Activate
    Sheets(1).Select
    Run_SumIf
    
    'Sheets("Lookup").Visible = Excel.XlSheetVisibility.xlSheetHidden
    
    MsgBox ("Done")
    
    'Application display alert was false changed to true to increase speed
    Application.DisplayAlerts = True
    
    End Sub


    Lorac1969

    Wednesday, June 1, 2016 6:34 PM

Answers

  • To create a named range based on a cell that you find, use code like this, which will name the range from the cell below "Payable" to the bottom of that column

            

        Dim rngC As Range

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

        ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=Range(rngC(2), Cells(ActiveSheet.Rows.Count, rngC.Column).End(xlUp))

    If you want to name the entire column of the found cell, use

      
        ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=rngC.EntireColumn

    Wednesday, June 1, 2016 8:40 PM
  • Hi David,

    Thanks for responding, but a reply from someone else answered my question and now my macro works.


    Lorac1969

    • Marked as answer by David_JunFeng Tuesday, June 14, 2016 2:16 PM
    Friday, June 3, 2016 7:48 PM

All replies

  • To create a named range based on a cell that you find, use code like this, which will name the range from the cell below "Payable" to the bottom of that column

            

        Dim rngC As Range

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

        ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=Range(rngC(2), Cells(ActiveSheet.Rows.Count, rngC.Column).End(xlUp))

    If you want to name the entire column of the found cell, use

      
        ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=rngC.EntireColumn

    Wednesday, June 1, 2016 8:40 PM
  • >>>Below is the code I have so far. Any suggestions? The current code isn't naming the column as a range, it names the entire sheet.

    According to your description, please correct me if I have any misunderstandings on your question. If you want to find a column by it's column name, refer to below:

    Cells.Find("Payable", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select


    If you want to not include the cell with "Company" in the selection, then use

    Cells.Find("Payable", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1).SpecialCells(xlCellTypeConstants).Select

    In addition could you provide more information about this issue, for example Excel file, screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.

    Thursday, June 2, 2016 8:35 AM
  • Hi Bernie,

    It worked perfectly! Thanks so much.


    Lorac1969

    Friday, June 3, 2016 7:47 PM
  • Hi David,

    Thanks for responding, but a reply from someone else answered my question and now my macro works.


    Lorac1969

    • Marked as answer by David_JunFeng Tuesday, June 14, 2016 2:16 PM
    Friday, June 3, 2016 7:48 PM
  • Hi Lorac1969,

    I am glad to hear you have resolved your issue, could you provide this solution, it will help other community member when they have same issue.

    Thanks for your understanding.
    Friday, June 10, 2016 9:57 AM