excel - 在函数中使用 for 循环变量

Question

问题:是否可以在函数中使用 for 循环变量？
我有以下代码:

For Each hcell In hRng
    '(something irrelevant happens)
    
    For Each tCell In tRng
        If tCell = "Name1" Then
            
            RowNum = tWb.Sheets("Sheet_" & hcell).Cells(Rows.Count, 2).End(xlUp).Row
            EmpCol = 1 + tWb.Sheets("Sheet_" & hcell).Cells(1, 1).End(xlToRight).Column
            
            tWb.Sheets("Sheet_" & hcell).Cells(1, EmpCol) = "Name1"

                For i = 2 To RowNum
                    tWb.Sheets("Sheet_" & hcell).Cells(i, EmpCol) = Application.IfNa(Application.VLookup(hcell & tWb.Sheets("Sheet_" & hcell).Cells(i, 2), _
                        sheetDiWs.Range("A2:I" & sheetDiWs.Cells(Rows.Count, 1).End(xlUp).Row), 3, False), "")
                Next i
        
        ElseIf tCell = "Name2" Then
            '(same process as above, but different name, different lookup column)
                
        ElseIf tCell = "Nam3" Then
            '(same process: different name, different lookup column)
        
        End If
    Next tCell
Next hCell

这是代码中不断重复的部分，我正在考虑使用一个函数而不是多次使用相同的代码:

RowNum = tWb.Sheets("Sheet_" & hcell).Cells(Rows.Count, 2).End(xlUp).Row
EmpCol = 1 + tWb.Sheets("Sheet_" & hcell).Cells(1, 1).End(xlToRight).Column

tWb.Sheets("Sheet_" & hcell).Cells(1, EmpCol) = "Name1"

    For i = 2 To RowNum
        tWb.Sheets("Sheet_" & hcell).Cells(i, EmpCol) = Application.IfNa(Application.VLookup(hcell & tWb.Sheets("Sheet_" & hcell).Cells(i, 2), _
            sheetDiWs.Range("A2:I" & sheetDiWs.Cells(Rows.Count, 1).End(xlUp).Row), 3, False), "")
    Next i

这里的问题是变量 细胞 值来自第一个 FOR LOOP。
有什么方法可以在我的(公共(public))函数中使用当前的 hcell 值？

Answer 1

实际上，您宁愿创建一个程序(子)而不是函数，并将您需要的变量作为参数提交。

Option Explicit

Sub test()
    For Each hCell In hRng
        '(something irrelevant happens)
        
        For Each tCell In tRng
            If tCell = "Name1" Then
                
                DoRepeatingJob tCell, hCell.Value, tWb, sheetDiWs
            
            ElseIf tCell = "Name2" Then
                '(same process as above, but different name, different lookup column)
                    
            ElseIf tCell = "Nam3" Then
                '(same process: different name, different lookup column)
            
            End If
        Next tCell
    Next hCell
End Sub

Public Sub DoRepeatingJob(ByVal tCell As String, ByVal hCell As String, ByVal tWb As Workbook, sheetDiWs As Worksheet)
    Dim RowNum As Long
    RowNum = tWb.Sheets("Sheet_" & hCell).Cells(tWb.Rows.Count, 2).End(xlUp).Row
    
    Dim EmpCol As Long
    EmpCol = 1 + tWb.Sheets("Sheet_" & hCell).Cells(1, 1).End(xlToRight).Column
                
    tWb.Sheets("Sheet_" & hCell).Cells(1, EmpCol) = tCell
    
    Dim i As Long
    For i = 2 To RowNum
        tWb.Sheets("Sheet_" & hCell).Cells(i, EmpCol) = Application.IfNa(Application.VLookup(hCell & tWb.Sheets("Sheet_" & hCell).Cells(i, 2), _
        sheetDiWs.Range("A2:I" & sheetDiWs.Cells(Rows.Count, 1).End(xlUp).Row), 3, False), "")
    Next i
End Sub

但实际上，如果代码是您发布的代码，则不需要这些 ElesIfs只需要调整这条线 tWb.Sheets("Sheet_" & hCell).Cells(1, EmpCol) = tCell所以它需要变量 tCell :

Sub test()
    For Each hCell In hRng
        '(something irrelevant happens)
        
        For Each tCell In tRng
            If tCell = "Name1" Or tCell = "Name2" Or tCell = "Name3" Then
                Dim RowNum As Long
                RowNum = tWb.Sheets("Sheet_" & hCell).Cells(tWb.Rows.Count, 2).End(xlUp).Row
                
                Dim EmpCol As Long
                EmpCol = 1 + tWb.Sheets("Sheet_" & hCell).Cells(1, 1).End(xlToRight).Column
                            
                tWb.Sheets("Sheet_" & hCell).Cells(1, EmpCol) = tCell
                
                Dim i As Long
                For i = 2 To RowNum
                    tWb.Sheets("Sheet_" & hCell).Cells(i, EmpCol) = Application.IfNa(Application.VLookup(hCell & tWb.Sheets("Sheet_" & hCell).Cells(i, 2), _
                    sheetDiWs.Range("A2:I" & sheetDiWs.Cells(Rows.Count, 1).End(xlUp).Row), 3, False), "")
                Next i
            End If
        Next tCell
    Next hCell
End Sub