Results 1 to 3 of 3

Problem: dynamic array from range.

Threaded View

  1. #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.

    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.
    Last edited by Mark K; 08-30-2006 at 04:33 PM.

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