Problem: dynamic array from range.

**Mark K** · 08-30-2006, 04:21 AM

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

**Mark K** · 08-30-2006, 04:24 PM

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.

Sub addSrc()
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 = "J4"
scrEnd = "U4"
' 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 - 1)
' Fill the array
  For scrCount = 0 To (scrTotal - 1)
    scrNum(scrCount) = Worksheets("MeetData").Cells(4, 10 + scrCount).Value
  Next scrCount
' Use the array
  For i = 0 To (scrTotal - 1)
    nRows = 0
    Worksheets("Temp").Select
    Range("A1").Select
' If array value greater than 1, change active cell
    If scrNum(i) > 1 Then
' Select start cell
        Cells((scrNum(i) - 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 i
End If
End Sub

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.

**Mark K** · 08-31-2006, 02:41 AM

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.

Problem: dynamic array from range.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Problem: dynamic array from range.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions