How to create dynamic ranges, better alternative to OFFSET

I have a Function that refers to a range and inputs the values that are Offset to that range into a field of cells.

My question is how to a set it up so that the range and the off set fields can be dynamic so that if I was to insert columns in the middle of the OffSets it would not impact the function?

here is my function:

'''declare variables'''
Dim range_ValidRows As Range
Dim int_FirstYear As Integer
Dim int_Frequency As Integer

Dim int_Spread As Integer
Dim long_Cost As Long
Dim long_CurrentRow As Long
Dim int_CurrentColumn As Integer
Dim int_FrequencyCount As Integer
Dim i As Integer

'''clear everything in columns 5-35'''
ActiveSheet.Range("o11:Am222").ClearContents

'''set the range of rows you are going to apply the method to'''
Set range_ValidRows = ActiveSheet.Range(Cells(12, 10), Cells(222, 10))



'''iterate through these rows, first checking that they aren't blank, then applying the method'''
For Each cell_Check In range_ValidRows

'''set the variables required for the method'''
int_FirstYear = cell_Check.Offset.Value
int_Frequency = cell_Check.Offset(0, 1).Value
int_Spread = cell_Check.Offset(0, 2).Value
long_CurrentRow = cell_Check.Row
int_CurrentColumn = 15
int_FrequencyCount = 0


''''check if first year cell is empty
If IsEmpty(ActiveSheet.Cells(long_CurrentRow, 10)) = False Then
If ActiveSheet.Cells(long_CurrentRow, 10).Value < 26 Then

long_Cost = cell_Check.Offset(0, 3).Value / cell_Check.Offset(0, 2).Value

'''iterate through the year 1-24 columns'''
For int_CurrentColumn = 15 To 39

'''check if the current column is the first year, or the first year plus a multiple of the frequency'''
If int_CurrentColumn - 14 = int_FirstYear + int_Frequency * int_FrequencyCount Then

'''set the value of the current column to the cost'''
ActiveSheet.Cells(long_CurrentRow, int_CurrentColumn).Value = long_Cost

'''add the cost to the adjacent cells for the number of times specified in the spread'''
For i = 1 To (int_Spread - 1)
'''check that the data won't be entered beyond column 35'''
If int_CurrentColumn + i <= 39 Then
ActiveSheet.Cells(long_CurrentRow, int_CurrentColumn + i).ClearContents
ActiveSheet.Cells(long_CurrentRow, int_CurrentColumn + i).Value = long_Cost
End If
Next i

'''increase the multiple of the frequency by 1'''
int_FrequencyCount = int_FrequencyCount + 1
Else
If IsEmpty(ActiveSheet.Cells(long_CurrentRow, int_CurrentColumn)) Then
ActiveSheet.Cells(long_CurrentRow, int_CurrentColumn).Value = 0
End If

End If

Next int_CurrentColumn
End If
End If

Next cell_Check

Application.ScreenUpdating = True

End Sub






Thanks

thanks for the response that sounds like a good idea, could you point me in the right direction for coding it.

ive attached a sample of the workbook.

I have another query as well If you have any ideas? I currently have a function that is called by a command button and generates the fields in the year coloumns, what id like to do is add it to a change_event procedure that would mean it would work automatically as numbers are altered adapt the year columns but ive no idea where to start with it, literally just started picking VBA up last week.


Thanksfrom_Function_to_Change_event.xlsm