VBA: Find column name and rename

[FryGirl]

I've been looking at some examples from the web and think I'm close, but apparently missing something.

I'm trying to rename specific column headers with the 2nd array. As a bonus, I would like to highlight that particular column header so I can delete everything else that is not a colored column header.

Sub aTest()
    Dim myArray1() As Variant
    Dim myArray2() As Variant
    Dim i As Long
    
    myArray1 = Array("Header 1", "Header 3", "Header 6", "Header 8", "Header 13")
    myArray2 = Array("Order #", "Quantity", "Amount", "Region", "Dest")
    
    With Sheets(1)
        For i = LBound(myArray1) To UBound(myArray1)
            .Rows(1).Find(myArray1(i)).Value = myArray2(i).Value
        Next i
    End With
    
End Sub

 

[Micron]

If all you want to do is have cols 1 through 5 to have those names, why bother looking for values to replace? Or try
.Rows(1).Replace what:=(myArray1(i)), Replacement:=myArray2(i), searchorder:=xlByColumns

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Sub aTest()
    Dim myArray(1 To 2) As Variant
    Dim i               As Long
    Dim rng             As Range
    
    myArray(1) = Array("Header 1", "Header 3", "Header 6", "Header 8", "Header 13")
    myArray(2) = Array("Order #", "Quantity", "Amount", "Region", "Dest")
    
    With Sheets(1)
        For i = LBound(myArray(1)) To UBound(myArray(1))
            Set rng = .Rows(1).Find(what:=myArray(1)(i), Lookat:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByColumns, MatchCase:=True)
            If Not rng Is Nothing Then
                With rng
                    .Value = myArray(2)(i)
                    .Interior.Color = vbYellow
                End With
            End If
            Set rng = Nothing
        Next i
    End With
    
End Sub

Dave

 

[FryGirl]

Thanks Dave, that works perfect. Thank you for your time.

 

[Micron]

I forgot about the coloring part and was going to ask why not just delete the columns that don't get renamed?
Isn't replace more efficient than looping over to find and then editing cell value?

 

[dmt32]

Thanks Dave, that works perfect. Thank you for your time.

welcome glad update does what you want & we were able to help

appreciate your feedback

Dave

 

[FryGirl]

Hi Micron, your suggestion worked great as well. Yes, delete the columns that do not get renamed is my intention, but I thought shading those cells would be the best way. Maybe not though.

 

[FryGirl]

Isn't replace more efficient than looping over to find and then editing cell value?

I surely do not know. Don't both of these suggestions loop?

 

[Micron]

Sorry, not clear enough I guess. Yes you have a loop because of the counter, but I meant setting a range object during that looping and then editing its value was akin to looping over a range. Perhaps it is not - I don't claim to be an Excel vba expert. The suggestion I made examines the whole range at once and replaces a value if found, but perhaps there is no real difference.

I'd recommend disabling events (but incorporate and error handler to ensure they are re-enabled) because if all you do is run that code, you might end up calling a lot of other code in the process. Maybe not now because you have none, but are you going to add things like sheet change event later? I also think if the array value is not found, you might as well just delete the column and forget about manually deleting it later, although I suspect you'd have to loop backwards (from right to left over columns).

 

[FryGirl]

Yes, I'm simply building a backwards loop right now to delete those unshaded columns. As for the events, yes, I have a procedure for that.