Reputation: 2300
I have columns that keep moving around, I am trying to rewrite some Macros to use the Header name. Having some trouble applying the code to the Sub.
Trying to replace this: Columns("EO:EO").Select
with
Columns("aCell:aCell").Select
Tried various other ways but I can get nothing to work.
Thanks
Function LCol(ColumnNumber As Long) As String
Dim ColNum As Integer
Dim ColLetters As String
ColNum = ColumnNumber
ColLetters = ""
Do
ColLetters = Chr(((ColNum - 1) Mod 26) + 65) & ColLetters
ColNum = Int((ColNum - ((ColNum - 1) Mod 26)) / 26)
Loop While ColNum > 0
LCol = ColLetters
End Function
The Sub
Sub RenameOther()
Dim strSearch As String
Dim aCell As Range
Dim colz As Range
strSearch = "attribute_4"
Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
'Trying to replace this: Columns("EO:EO").Select
Columns("aCell:aCell").Select
Selection.Replace What:="Client/Customer/Other (optional)", Replacement:="Other", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Cells(2, aCell.Column).Select
End If
End Sub
Upvotes: 0
Views: 306
Reputation: 166980
Sub Tester()
ReplaceColumnContent "attribute_4", "Client/Customer/Other (optional)", "Other"
End Sub
Sub ReplaceColumnContent(colHeader As String, LookFor As String, ReplaceWith As String)
Dim strSearch As String
Dim aCell As Range
Dim colz As Range
Set aCell = Sheet1.Rows(1).Find(What:=colHeader, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
With Sheet1.Range(aCell.Offset(1, 0), Sheet1.Cells(Rows.Count, aCell.Column).End(xlUp))
.Replace What:=LookFor, Replacement:=ReplaceWith, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With
'aCell.offset(1,0).select
End If
End Sub
Upvotes: 1
Reputation: 19087
Alternatively you can try this:
aCell.EntireColumn.Select
Upvotes: 1
Reputation: 2430
'Trying to replace this: Columns("EO:EO").Select
Columns(aCell.Column).Select
Upvotes: 1