VBA中值相等时的IF语句循环及DataEntry子过程技术咨询

Let's walk through refining your incomplete DataEntry sub to properly implement loops that match values and handle your data entry task.

Key Issues in Your Original Code #

• Variable Declaration: Dim col, ro, col2 As Double is incorrect in VBA—only col2 gets typed as Double, while col and ro default to Variant. Since we're working with row/column numbers, use Long (integer type) instead.
• Early Exit in Column Loop: The Exit For when finding col2 could stop the loop before locating col, leading to missed date matches.
• Incomplete Row Loop: Your second loop cuts off mid-condition; we'll finish that logic to find the row matching Range("F17").

Corrected & Complete Code #

Here's the full, commented version with robust loop logic:

Sub DataEntry()
    ' Declare variables with appropriate types (Long for row/column indices)
    Dim col As Long, ro As Long, col2 As Long
    Dim sCellVal As String
    Dim cel As Range
    
    ' Initialize counters to 0 (used to check if matches were found)
    col = 0
    col2 = 0
    ro = 0
    
    ' Grab the shift type from D9
    sCellVal = Range("D9").Value
    
    If sCellVal Like "Night" Then
        ' First loop: Find columns in Service!C40:NR40 matching B9 and C9
        For Each cel In Worksheets("Service").Range("C40:NR40")
            ' Match B9 value to get the main column
            If cel.Value = Range("B9").Value Then
                col = cel.Column
            ' Match C9 value to get the secondary column
            ElseIf cel.Value = Range("C9").Value Then
                col2 = cel.Column
            End If
            ' Optional: Exit early if both columns are found to save time
            If col <> 0 And col2 <> 0 Then Exit For
        Next cel
        
        ' Check if the date (B9 value) was found
        If col = 0 Then
            MsgBox "Date Not Found"
            Exit Sub
        End If
        
        ' Second loop: Find row in Service!A40:A80 matching F17 value
        For Each cel In Worksheets("Service").Range("A40:A80")
            If cel.Value = Range("F17").Value Then
                ro = cel.Row
                Exit For ' Stop searching once we find the matching row
            End If
        Next cel
        
        ' Check if the row matching F17 was found
        If ro = 0 Then
            MsgBox "Row Matching F17 Not Found"
            Exit Sub
        End If
        
        ' Example: Enter your data into the found row/columns
        ' Adjust this section to match your actual data entry needs
        Worksheets("Service").Cells(ro, col).Value = "Night Shift Entry"
        If col2 <> 0 Then
            Worksheets("Service").Cells(ro, col2).Value = "Related Night Data"
        End If
        
    Else
        ' Optional: Handle non-night shift cases if needed
        MsgBox "This entry is not for a Night shift"
    End If
End Sub

Key Logic Breakdown #

• Column Search: The loop scans Service!C40:NR40 to find columns matching your B9 and C9 values. We only exit early if both matches are found (remove that condition if you need to check the entire range).
• Row Search: The second loop locates the row in Service!A40:A80 that matches F17 and stops immediately once found.
• Error Handling: We add checks for missing matches (col=0 or ro=0) to show user-friendly messages and exit the sub cleanly.
• Data Entry: The final section provides an example of inserting data into the intersection of your found row and columns—tweak this to fit your specific use case.

Tips for Improvement #

• Add Option Explicit at the top of your module to catch undeclared variables (prevents silly bugs).
• For dynamic ranges (e.g., rows that grow beyond 80), calculate the last used row instead of hardcoding:

  Dim LastRow As Long
  LastRow = Worksheets("Service").Cells(Rows.Count, "A").End(xlUp).Row
  

  Then use Range("A40:A" & LastRow) in your loop.
• For large datasets, load the range into an array instead of looping through each cell—it’s much faster.

内容的提问来源于stack exchange，提问作者Dtex