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