+ Reply to Thread
Results 1 to 3 of 3

Problem: dynamic array from range.

  1. #1
    Registered User
    Join Date
    09-06-2004
    Posts
    37

    Problem: dynamic array from range.

    I'm trying to fill a dynamic array from a range but am having problems.

    The active sheet is 'Temp' and I'm trying to fill the array (scrNum) with values in the range J4 to U4 from the sheet 'MeetData'.

    About half the time there will be no entries in the range, so the array will be empty, other times there will be 1 or more numbers. The cells are filled from left to right, so the first empty cell marks the end of data in the range.

    I tried using Worksheets("MeetData").Range("J4", "U4").Count to get the size of the array (is this correct?), but get lost when trying to actually fill it with the data. Should I be selecting the start of the range ("J4") then using offsets to specify the data or is there a simpler/better way?

    TIA

  2. #2
    Registered User
    Join Date
    09-06-2004
    Posts
    37
    Ok, Think I worked this out for myself, but any tips or ideas are more than welcome. Using the following to fill a dynamic array (from sheet "MeetData"), then, if there is data for the array, add rows to the sheet "Temp" based on the data in the array. Surprisingly, this actually seems to work.

    Please Login or Register  to view this content.
    Now I just need to get to work figuring out how to get data to the "Temp" worksheet from csv files and loop through doing this to each set of data.
    Last edited by Mark K; 08-30-2006 at 04:33 PM.

  3. #3
    Registered User
    Join Date
    09-06-2004
    Posts
    37
    Ok, not as smart as I thought I was. While the above appeared to work when run on it's own, I've now included it in a larger project and it doesn't work as expected. Here's what I currently have:

    Sub addScr()
    Dim scrNum() 'Dynamic Array
    Dim scrStart As String, scrEnd As String, scrRange As String
    Dim scrTotal As Integer, nRows As Integer, scrCount As Integer

    ' These will be part of larger loop later, so need to be variable
    scrStart = "J" & sCnt
    scrEnd = "U" & sCnt
    ' Count number of non-empty cells in range for array
    scrTotal = WorksheetFunction.CountIf(Worksheets("MeetData").Range(scrStart, scrEnd), ">0")
    ' Only do something if there is data in range for the array
    If scrTotal > 0 Then
    ReDim scrNum(scrTotal)
    ' Fill the array
    For scrCount = 0 To scrTotal - 1
    scrNum(scrCount) = Worksheets("MeetData").Cells(4, 10 + scrCount)
    Next scrCount
    ' Use the array
    For iScr = 0 To scrTotal - 1
    nRows = 0
    Worksheets("Temp").Select
    Range("A1").Select
    ' If array value greater than 1, change active cell
    If scrNum(iScr) > 1 Then
    ' Select start cell
    Cells((scrNum(iScr) - 1) * 24, "A").Select
    ' Add reqired rows
    Do Until nRows = 24
    ActiveCell.EntireRow.Insert shift:=xlDown
    nRows = nRows + 1
    Loop
    ActiveCell.Offset(1, 0).Value = "SCR"
    ' Otherwise, start where we are
    Else
    ' Add required rows
    Do Until nRows = 24
    ActiveCell.EntireRow.Insert shift:=xlDown
    nRows = nRows + 1
    Loop
    ActiveCell.Value = "SCR"
    End If
    Next iScr
    End If
    End Sub

    It seems to find the data for the array without any problems, but instead of moving the insertion point for the rows based on the data, it's putting all the extra rows under "A1". So either I'm not getting the data to load into the array correctly or not reading it correctly.

    Any help appreciated.

+ Reply to Thread

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