+ Reply to Thread
Results 1 to 3 of 3

How to create dynamic ranges, better alternative to OFFSET

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2003

    Talking 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'''

    '''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
    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


  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    MS-Off Ver
    Office 2010

    Re: How to create dynamic ranges, better alternative to OFFSET

    One way is to search for column headers. Hopefully they are unique. Then you can assign the column number to a variable and use the variable in the offset.

    But without a sample sheet, I'm just guessing.

    Please use code tags around your code on future postings.
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    MS-Off Ver
    Excel 2003

    Re: How to create dynamic ranges, better alternative to OFFSET

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1