• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

如何从表头中遍历列vba

vba 来源:San 11次浏览

我是VBA的新手,我写了一段代码来突出显示我excel文件中的一些内容。但我有一个问题,我想从vba中的表标题遍历列,而我无法做到这一点,因为我的列号未来可能会更改。请帮忙。如何从表头中遍历列vba

以下是我的代码 –

谢谢!

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 


===========解决方案如下:

还没有通过你的代码,但你可以从WorksheetObject.Range("TableName[HeaderCaption]").Column访问实际的列。

说我有一个表 “表1”(的ListObject)在Sheet与Dim oWS as Worksheet; Set oWS = Thisworkbook.Worksheets("Sheet1"),则:a特定部首的oWS.Range("Table1[#Headers]").Address

  • 柱:oWS.Range("Table1[MyHeader]").Column
    • 头地址可接

    Ref:ListObject Object (Excel)


    版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
    喜欢 (0)