CODEWITHSUNDEEP
excelvba
xyz
xyz

Reputation: 2300

Using the column Header to column letter code to set range in Macro

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

Answers (3)

Tim Williams
Tim Williams

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

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

Alternatively you can try this:

aCell.EntireColumn.Select

Upvotes: 1

mr.Reband
mr.Reband

Reputation: 2430

  'Trying to replace this: Columns("EO:EO").Select
  Columns(aCell.Column).Select

Upvotes: 1

Related Questions