☰

将代码分配给列标题而不是列单元格

tag-icon • tag-icon microsoft-excel vba
将代码分配给列标题而不是列单元格

大家好，我需要帮助，我想将我的代码分配给列标题而不是列单元格。但我不知道该怎么做。

以下是一些信息

Worksheet = "Export Worksheet"    
Column 14's header = "PACKAGE"    
Column 10's header = "DAY_SCHEDULE"    
Column 11's header = "START_TIME"    
Colum 27's header = "LUNCH"

以下是我的编码，没有为列标题分配任何代码。请帮忙！

Sub Lunch()

Dim i, NumberOfRows As Integer

   With ActiveSheet
    NumberOfRows = .Cells(.Rows.Count, "B").End(xlUp).Row
   End With

For i = 2 To NumberOfRows
    If (((Cells(i, 14).Value <> "" And Cells(i, 14).Value = Cells(i + 1, 14).Value) And (Cells(i, 10).Value = Cells(i + 1, 10).Value) And (Cells(i + 1, 11).Value - Cells(i, 11).Value) > 120)) Then
        Cells(i, 27).Value = "TRUE"
    Else
        Cells(i, 27).Value = "FALSE"
    End If
Next

答案1

以下内容应该可以帮您找到您想要的东西：

Sub Lunch()
    Dim i As Integer
    Dim NumberOfRows As Integer
    Dim ws As Worksheet
    Dim Package As Variant
    Dim DaySchedule As Variant
    Dim StartTime As Variant
    Dim Lunch As Variant

    Set ws = ActiveSheet

'Set Variables
    Package = Application.Match("PACKAGE", ws.Range("1:1"), 0)
    DaySchedule = Application.Match("DAY_SCHEDULE", ws.Range("1:1"), 0)
    StartTime = Application.Match("START_TIME", ws.Range("1:1"), 0)
    Lunch = Application.Match("LUNCH", ws.Range("1:1"), 0)

'Check for missing headers
    If IsError(Package) Or IsError(DaySchedule) Or IsError(StartTime) Or IsError(Lunch) Then
        MsgBox "One or more of the required headers are missing!", vbCritical, "Error"
        Exit Sub
    End If

    With ws
        NumberOfRows = .Cells(.Rows.Count, "B").End(xlUp).Row
'Main Loop
        For i = 2 To NumberOfRows
            If (((.Cells(i, Package) <> "" And .Cells(i, Package) = .Cells(i + 1, Package)) And _
                    (.Cells(i, DaySchedule) = .Cells(i + 1, DaySchedule)) And _
                    (.Cells(i + 1, StartTime) - .Cells(i, StartTime)) > 120)) Then
                .Cells(i, Lunch).Value = "TRUE"
            Else
                .Cells(i, Lunch).Value = "FALSE"
            End If
        Next
    End With

End Sub

假设您的列标题位于第 1 行。

相关内容