Consulting

Results 1 to 16 of 16

Thread: Changing Column Headers

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Nov 2019
    Posts
    8
    Location

    Changing Column Headers

    This Excel VBA Code, shown below was previously posted in 2016 and it worked, but now it doesn't. The column headers are all renamed and then I get this error: "Object Variable or With block variable not set". Debug highlights the row which is shown in BOLD. I'm not sure where to look?

    Sub ChangeColumnHeaders()
    'ONE  - This sub renames all of the column headers from NSS that are applicable to the access import AND
    'Inserts all additional columns and names the column headers
    
    
      
        Dim ColHeads
        Dim i As Long
    
    
        Sheets("Shelter").Select
        ColHeads = Worksheets("Column_Headers").Cells(1).CurrentRegion.Value
    
    
        With Worksheets("Shelter")
            For i = LBound(ColHeads, 1) To UBound(ColHeads, 1)
                .Rows(1).Find(ColHeads(i, 1)).Value = ColHeads(i, 2)
           
            Next i
        End With
        
    
    
        'Insert Column to the left of Column D 'SSecondaryPhoneType to the right of SSecondary Phone
        Columns("E:E").Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        Worksheets("Shelter").Range("E1").Value = "SSecondaryPhoneType"
    
    
        'Insert Column to the left of Column Z 'CPrimaryPhoneType to the right of CPrimaryPhoneExt
        Columns("Z:Z").Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        Worksheets("Shelter").Range("Z1").Value = "CPrimaryPhoneType"
    
    
    
    
        'Insert 2 Columns to the left of Column AB1 & AC1 ''CAlternatePhoneType to the right of CAlernatePhone
        Columns("AB:AC").Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        Worksheets("Shelter").Range("AB1").Value = "CAlternatePhoneType"
        Worksheets("Shelter").Range("AC1").Value = "CAlternatePhoneExt"
    
    
        'Insert 1 Columns to the left of Column AJ1 '
        Columns("AJ:AJ").Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        Worksheets("Shelter").Range("AJ1").Value = "24HrPOCPhoneType"
    
    
    
    
       'Insert 1 Columns to the left of Column AQ1
        Columns("AQ:AQ").Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        Worksheets("Shelter").Range("AQ1").Value = "AlternateContact1PhoneType"
    
    
    
    
        'Insert 1 Columns to the left of Column AX1
        Columns("AX:AX").Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        Worksheets("Shelter").Range("AX1").Value = "AlternateContact2PhoneType"
    
    
    Call RemovePhoneDashes
    End Sub 'Change_and_Rename_Headers_
    Last edited by Paul_Hossler; 11-18-2019 at 07:44 AM.

Posting Permissions

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