CODEWITHSUNDEEP
vba
San
San

Reputation: 1

how to traverse column from table header in vba

I am new to VBA and I have written a code to highlight few things in my excel file. But I have one problem I want to traverse column from table header in vba and I am not able to do it as my column number may change in future. Please help.

Below is my code-

Thank you!

Sub LoopThroughRows()

    Application.EnableCancelKey = xlDisabled
    Dim k As Long, lastrow As Long, lastCol As Long, i As Integer, j As Integer, CurrentYear As String, TableHeader As String, CurrentQuarter As String, TargetYear As String, TargetQuarter As String
    lastCol = Range("AA1").End(xlToRight).Column

    With Worksheets("Sheet1")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    Application.ScreenUpdating = False

    For j = 2 To lastrow                              'Starting the loop from the 2nd row
        For i = 27 To lastCol                         ' Starting the loop from the 27th column AA1
            With Worksheets("Sheet1")
                If .Cells(j, i).Value > 0 Then
                    TableHeader = Cells(1, i).Text
                    Exit For
                End If
            End With
        Next                                          'Loop to traverse columns ends if the condition is met

        CurrentYear = Right(Cells(1, i), 2)           ' Extracting the last 2 characters
        CurrentQuarter = Mid(Cells(1, i), 2, 1)       'Extracting the Quarter number 2nd character
        TargetYear = Right(Range("R" & j), 2)         'Extracting the last 2 characters
        TargetQuarter = Right(Range("Q" & j), 1)      'Extracting the quarter number
        Range("BX1" & j) = "Status"


        If Not IsNull(CurrentYear & TargetYear) Then
            If CurrentYear < TargetYear Then
                Range("A" & j).EntireRow.Interior.ColorIndex = 3
                Range("BX" & j) = "Early Start"
            ElseIf TargetYear = CurrentYear Then
                If CurrentQuarter < TargetQuarter Then
                    Range("A" & j).EntireRow.Interior.ColorIndex = 3
                    Range("BX" & j) = "Early Start"
                End If
            End If

        End If

        If Not IsNull(CurrentYear & TargetYear) Then
            If CurrentYear > TargetYear Then
                Range("A" & j).EntireRow.Interior.ColorIndex = 6
                Range("BX" & j) = "Late Start"
            ElseIf TargetYear = CurrentYear Then
                If CurrentQuarter > TargetQuarter Then
                    Range("A" & j).EntireRow.Interior.ColorIndex = 6
                    Range("BX" & j) = "Late Start"
                End If
            End If

        End If

        If WorksheetFunction.Sum(Range("AA" & j & ":BW" & lastCol)) = 0 Then
            Range("A" & j).EntireRow.Interior.ColorIndex = 5
            Range("BX" & j) = "Not Started"
        End If
    Next                                              ' Moving on to next row
    Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 101

Answers (1)

PatricK
PatricK

Reputation: 6433

Have not gone through your code, but you can access the actual Column from WorksheetObject.Range("TableName[HeaderCaption]").Column.

Say I have a Table "Table1" (ListObject) in Sheet1 with Dim oWS as Worksheet; Set oWS = Thisworkbook.Worksheets("Sheet1"), then:

  • Header address can be accessed by: oWS.Range("Table1[#Headers]").Address
  • Column of a particular Header: oWS.Range("Table1[MyHeader]").Column

Ref: ListObject Object (Excel)

Upvotes: 1

Related Questions