Passing a table to a 'function'

[Dtex20]

Hi guys,

I've got this sub that acts like a function, i want to pass another table to it since i want my sub to do the exact same thing to two tablse. A code snippet from my code below.

 For Each nameRNG In [table8[Employee Number]].Rows   
   emp_num_ser = nameRNG.Value
   emp_row_ser = nameRNG.Row
     For Each numRNG In *****.Range("B3:B10000")
       If emp_num_ser = numRNG Then
         emp_row_win = numRNG.Row
         Set emp_pay = service_wsh.Cells(emp_row_ser, month_col)
         emp_pay.Resize(, 5).Copy
         ******.Cells(emp_row_win, 14).PasteSpecial xlPasteValues
         Application.CutCopyMode = False

I already know how to pass variables, i just want to know what i need to store the tables as so i can pass it to this sub.

Thanks

 

[John_w]

In your code [table8[Employee Number]] is a Range, therefore:

Sub Caller()
    YourTableFunction [table8[Employee Number]]
End Sub

Function YourTableFunction(theTableColumn As Range)
    Dim nameRNG As Range
    For Each nameRNG In theTableColumn.Rows
        Debug.Print nameRNG.Address, nameRNG.Value
    Next
End Function

A table is a ListObject, which is a member of the ListObjects collection of the sheet on which the table exists, and you can reference the table and column and pass it to the same function like this:

Sub Caller()
    YourTableFunction Worksheets("name of sheet where table exists").ListObjects("table8").ListColumns("Employeed Number").Range
End Sub

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables shows how to reference the different parts of a table.