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

## Bookmarks