Setting column number and row number to variables leads to lots of issues

[enve_eng]

Thanks for viewing my post. A task that I am that I am trying to do now is likely causing me problems because both my column number and/or row number are dynamic. Therefore, they are each set to a variable and change based on where they are in various loops, etc. Is there a way to regularly refer to row numbers and column numbers when they are set to variables? I seem to only be able to do it with copying and pasting.

Here is one case where I am having a lot of difficulty. I am copying a dynamic range of cells (the range changes based on the format controls specified), and then having that range be pasted into an output table. Below is PART of the code (therefore variables, etc. are dim'd but not shown). The problem arises when I want to average the values of the pasted cells.

'EXTRACTING and ADDING ORIGINAL DATES IN INPUT RANGE (DIST)
'tab_name is the desired sheet to copy from. It varies within a loop.

Sheets(tab_name).Select
Range(Cells(start_date_row, date_column_num), Cells(end_date_row, date_column_num)).Select 'Here variables work fine
Selection.Copy
Sheets("Table_Output").Select
Cells(4, 2).Select
ActiveSheet.Paste


'Adding values that coincide with the data range
'Values are always pasted starting in row 4, but varying in column number (param_col_index) (loop not shown, but indexes based on the number of parameter the user selects in format control check boxes).
Sheets(tab_name).Select
Range(Cells(start_date_row, param_col), Cells(end_date_row, param_col)).Select 'Variables still work here
Selection.Copy
Sheets("Table_Output").Select
Cells(4, param_col_index).Select
ActiveSheet.Paste

'Calculating Avg : CALCULATING THE AVERAGE IS WHERE I CANNOT FIGURE HOW TO APPLY THE VARIABLE COLUMN/ROW NUMBER
'table_first_empty_row = (Range("B" & Rows.Count).End(xlUp).Row) + 1 'determining first empty row in output table
'Debug.Print table_first_empty_row


'Cells(table_first_empty_row, param_col_index) = AVERAGE?????
Want the average of cells (4, param_col_index) to cells ((table_first_empty_row - 1), param_col_index)


Any help would greatly be appreciated! Thank you!

[westconn1]

try like

msgbox = worksheetfunction.average( range(cells (4, param_col_index), cells (table_first_empty_row - 1, param_col_index)).address)