TC Counter -- identify and count number of instances in a selected column.

Hello,

I am trying to creat a VBA macro to locate cells in a manually selected column that have the text value "TC" and if this value is found then the cell value will be changed to TC1, then TC2, TC3, TC4, etc... (up to a max count of 1024).

I'm starting out small (beginner to VBA) and trying to get TC to increment across a small population of a predefined column. This is what I have so far (though each of the 66 TC values in the column are renamed TC1 and do not increment):

Sub TC_Count()


For N = 1 To 66
For Count = 1 To 66

    If Range("C" & N).Value = "TC" Then

            Range("C" & N).Select
            ActiveCell.Value = ActiveCell.Text & Count


        Else
    
    End If
    

Next Count
Next N

End Sub

Any help is appreciated. Thank you.

Something like this.

Sub TC()
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
Dim WhatColumn As String
Dim iCount As Long

    If Selection.Columns.Count > 1 Then Exit Sub
    WhatColumn = Mid(ActiveCell.Address, 2, InStr(2, ActiveCell.Address, "$") - InStr(1, ActiveCell.Address, "$") - 1)
    With Range(WhatColumn & ":" & WhatColumn)
        Set LastCell = .Cells(.Cells.Count)
    End With
    Set FoundCell = Range(WhatColumn & ":" & WhatColumn).Find(What:="TC", After:=LastCell)
    
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    Do Until FoundCell Is Nothing
        iCount = iCount + 1
        FoundCell.Value = FoundCell.Value & iCount
        Set FoundCell = Range(WhatColumn & ":" & WhatColumn).FindNext(After:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
    Loop
End Sub

It just takes the $ sign out of cell address and drops the number, so we get the Column Letter of the activecell. Test this

Sub ActiveCellColumnLetter()
 MsgBox ActiveCell.Address
 MsgBox Split(ActiveCell.Address, "$")(1)
End Sub

Try this

Sub TC_v2()
 Dim FoundCell As Range
 Dim LastCell As Range
 Dim FirstAddr As String
 Dim WhatColumn As String
 Dim iCount As Long

    If Selection.Columns.Count > 1 Then Exit Sub
    WhatColumn = Split(ActiveCell.Address, "$")(1)
    With Range(WhatColumn & ":" & WhatColumn)
        Set LastCell = .Cells(.Cells.Count)
    End With
    Set FoundCell = Range(WhatColumn & ":" & WhatColumn).Find(What:="TC", After:=LastCell)
    
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    Do Until FoundCell Is Nothing
        If Not Left$(FoundCell.Value, 3) = "TCS" Then
            iCount = iCount + 1
            FoundCell.Value = FoundCell.Value & iCount
            Set FoundCell = Range(WhatColumn & ":" & WhatColumn).FindNext(After:=FoundCell)
        End If
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
    Loop
End Sub

Opps I put the end if in the wrong spot try this, Put the End if above Set FoundCell

Sub TC_v2()
 Dim FoundCell As Range
 Dim LastCell As Range
 Dim FirstAddr As String
 Dim WhatColumn As String
 Dim iCount As Long

    If Selection.Columns.Count > 1 Then Exit Sub
    WhatColumn = Split(ActiveCell.Address, "$")(1)
    With Range(WhatColumn & ":" & WhatColumn)
        Set LastCell = .Cells(.Cells.Count)
    End With
    Set FoundCell = Range(WhatColumn & ":" & WhatColumn).Find(What:="TC", After:=LastCell)
    
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    Do Until FoundCell Is Nothing
        If Not Left$(FoundCell.Value, 3) = "TCS" Then
            iCount = iCount + 1
            FoundCell.Value = FoundCell.Value & iCount
        End If
        Set FoundCell = Range(WhatColumn & ":" & WhatColumn).FindNext(After:=FoundCell)
        
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
    Loop
End Sub

Or even this added LookAt:=xlWhole to only look at TC

Sub TC_v3()
 Dim FoundCell As Range
 Dim LastCell As Range
 Dim FirstAddr As String
 Dim WhatColumn As String
 Dim iCount As Long

    If Selection.Columns.Count > 1 Then Exit Sub
    WhatColumn = Split(ActiveCell.Address, "$")(1)
    With Range(WhatColumn & ":" & WhatColumn)
        Set LastCell = .Cells(.Cells.Count)
    End With
    Set FoundCell = Range(WhatColumn & ":" & WhatColumn).Find(What:="TC", After:=LastCell, LookAt:=xlWhole)
    
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    Do Until FoundCell Is Nothing
        iCount = iCount + 1
        FoundCell.Value = FoundCell.Value & iCount
        Set FoundCell = Range(WhatColumn & ":" & WhatColumn).FindNext(After:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
    Loop
End Sub