Results 1 to 5 of 5

For each cell in range loop does not recognize expanding range

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    For each cell in range loop does not recognize expanding range

    Help!!!

    Have the following code:

    Sub BuildSortList()
    Dim cell As Range
    Dim cell2 As Range
    Dim val As String
    Dim x As Long
    
    Sheets("Sheet1").Select
    x = Range("panel_sort").Rows.Count
        If x > 1 Then
            Range("panel_sort").Offset(1, 0).Resize(x - 1, 1).Delete
        End If
    
    For Each cell In Range("panel_sort")
        val = cell.Value
        For Each cell2 In Range("D3:D12")
            x = Range("panel_sort").Rows.Count
            If cell2.Value = val Then
                Range("panel_sort").Offset(x, 0).Resize(1, 1).Value = cell2.Offset(0, -1).Value
            End If
        Next cell2
    Next cell
    
    End Sub
    Essentially it deletes all but first (top) cell in a named range (panel_sort) and rebuilds it (just in case there are any changes in source data). It works fine for the first set of loops to lookup the first cell in the source data and return vlaues indexed one column to left and put those values in the named range. The next step is to do a lookup of the second cell in the named range... but the For Each loop doesn't recognize that the named range has grown since the first loop.

    Being a VBA noob, I don't know how to fix it....
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Bookmarks

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