1

Question: Is it possible to use a for loop variable in a function?

I have the following code:

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

This is the part of the code that keeps repeating, and I was thinking of using a function instead of having the same code a bunch of times:

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

The issue here is that the variable hcell value comes from the first FOR LOOP.

Is there any way I can use the current hcell value in my (public) function?

Improve this question
2
  • 1
    A bit unclear, but yes, you can create a function where you pass the hcell variable to it for it to use... is that what you are asking? Commented Feb 5, 2021 at 9:44
  • Actually you would create a sub not a function. Commented Feb 5, 2021 at 10:08

1 Answer 1

Reset to default
2

Actually you would rather create a proceduer (sub) than a function, and submit the variables you need as arguments.

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

But actually if the code is what you posted you don't need these ElesIfs and just need to adjust this line tWb.Sheets("Sheet_" & hCell).Cells(1, EmpCol) = tCell so it takes the variable 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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.