(Solved) – how to find the column address(only the letter) in VBA and express columns function with variables?

(solved)-–-how-to-find-the-column-address(only-the-letter)-in-vba-and-express-columns-function-with-variables?

I am trying to find and replace part of my string using VBA within a certain range. For example, whenever my code found the word “CL”, I want it to replace the sigma headers from next column onwards with “ideal sigma”. But how do i find the cell’s column letter after i found the cell address which contain letters and numbers? At the same time im also not sure whether i express my columns() function correctly with variables…

Sub testing2()

Dim i As String
    Dim k As String
    Dim lastcolumn As Long
    Dim lastcolletter As String
    Dim firstcolletter As String
    Dim detailedcolletter As String
    Dim ra As Range

lastcolumn = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column

Dim ra As Range

Set ra = Worksheets("Data").Cells.Find(What:="CL", LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    detailedcolletter = ra.Address 'detailedcolletter address = D2 here
    'how to extract just the letter here only?

  i = "Sigma"
k = "Ideal Sigma"

lastcolletter = Col_Letter(lastcolumn)
Columns("firstcolletter" & ":" & "lastcolletter").Replace What:=i, replacement:=k, LookAt:=xlPart, MatchCase:=False
'not sure whether i expressed columns() function with variables correctly here

End Sub


Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Worksheets("Data").Cells(1, lngCol).Address(True, False), "$")

End Function

Currently

enter image description here

Expected

enter image description here

Updated code

Sub testing2()

Dim i As String
    Dim k As String
    Dim lastcolumn As Long
    Dim lastcolletter As String
    Dim firstcolletter As String
    Dim detailedcolletter As String
    Dim ra As Range

    lastcolumn = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column

    Set ra = Cells.Find(What:="CL", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        detailedcolletter = ra.Address 'detailedcolletter address = D4 here
        'how to extract just the letter here only?
        detailedcolletter = colLetter(ra.Column)

      i = "Sigma"
    k = "Ideal Sigma"

    lastcolletter = Col_Letter(lastcolumn)
    Columns("detailedcolletter" & ":" & "lastcolletter").Replace What:=i, Replacement:=k, LookAt:=xlPart, MatchCase:=False '<----type mismatch over here

    End Sub


Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")

End Function

Function colLetter(col As Long) As String
    colLetter = Split(Columns(col).Address(, 0), ":")(0)
End Function

latest pic
enter image description here

current pic

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *