Populating An Array From Loops

bhsoundman

Board Regular
Joined
Jul 17, 2010
Messages
61
Office Version
  1. 365
Platform
  1. MacOS
Hi All,

I'm new to arrays & I'm to the point that I'm over thinking this, but i'm determined to get it sorted out. In this script works correctly through the first loop. The second loop does fine finding the data, but I've reworked this so many times that I'm getting confused. The first loop (Find_The_Job_Loop) finds the correct column that has all of the potential data and then exits to the 2nd loop (Job_Name_Data_Loop) that's supposed to gather the data (Job_Data) from the rows in that found column from the first loop. Then it should deposit the data into the array (Crew_Destination_Array) along with whatever is in column B of the corresponding row.

I've gotten every error under the sun. I just can't get the data into the array correctly. Can someone set me straight? I'm confident that it's going to be somethign stupid, but I want to move on.

As always, thank you in advance!

VBA Code:
Sub Populate()


Dim Position_Array() As Variant
Dim Find_The_Job_Loop As Long
Dim Crew_Array_Range As Range
Dim Job_Name_Data_Loop As Long
Dim Job_Position_Data_Loop

Dim Job_Data As Range
Dim Crew_Array_Column As Long
Dim Crew_Array_Row As Long
Dim Crew_Position As String
Dim Crew_Destination_Array() As Variant
Dim i As Integer

Crew_Array_Column = 1
Crew_Array_Row = 1
i = 1

Sheets("Crew Builder").Activate

        For Find_The_Job_Loop = 4 To 2000 Step 32
                If Sheets("Crew Builder").Cells(25, Find_The_Job_Loop).Value <> "" Then
                Exit For
                End If
        Next Find_The_Job_Loop

Set Crew_Array_Range = Sheets("Crew Builder").Range(Cells(30, Find_The_Job_Loop), Cells(239, Find_The_Job_Loop))

        For Job_Name_Data_Loop = 1 To 200
                If WorksheetFunction.CountA(Sheets("Crew Builder").Cells(Job_Name_Data_Loop, Find_The_Job_Loop)) > 1 Then
                        Set Job_Data = Sheets("Crew Builder").Cells(Job_Name_Data_Loop, Find_The_Job_Loop)
                        Set Job_Position_Data_Loop = Sheets("Crew Builder").Cells(Job_Name_Data_Loop, 2)
                        Crew_Destination_Array(i, 1) = Job_Data.Value
                        Crew_Destination_Array(i, 2) = Job_Position_Data_Loop
                        i = i + 1
                End If
        Next Job_Name_Data_Loop

ReDim Crew_Destination_Array(0 To i)

MsgBox "finished"


End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Giving us a code that does not do what you want and no precise explanation of what it should do other than ..
supposed to gather the data (Job_Data) from the rows in that found column from the first loop. Then it should deposit the data into the array (Crew_Destination_Array) along with whatever is in column B of the corresponding row.
.. isn't much help.

It seems that once you have determined the relevant column to look at, then some (or is it all?) of the rows from row 30(?) to row 239 (or is it 229 being 200 rows from row 30?) should get values from the relevant column and column B into an array?

Assuming what I have said above is correct, can you clarify in words, including how we determine which rows get put into the array and which rows do not?
 
Upvote 0
I am not sure what you are trying to do with this line:
If WorksheetFunction.CountA(Sheets("Crew Builder").Cells(Job_Name_Data_Loop, Find_The_Job_Loop)) > 1 Then
But it is only looking at a single cell so it is never going to be ">1"
 
Upvote 0

I apologize that my explanation wasn't thorough enough. Yes, what you said is correct. in the relevant column, it should return only rows that are not empty.

Thanks for having a look.
 
Upvote 0
I am not sure what you are trying to do with this line:
If WorksheetFunction.CountA(Sheets("Crew Builder").Cells(Job_Name_Data_Loop, Find_The_Job_Loop)) > 1 Then
But it is only looking at a single cell so it is never going to be ">1"

It should be looking only for cells in the Find_The_Job_Loop column. I'm assuming I should change that to <> "" ? or maybe the CountA function already considers that?
 
Upvote 0
in the relevant column,
Yes, but where in the column?

Rich (BB code):
Set Crew_Array_Range = Sheets("Crew Builder").Range(Cells(30, Find_The_Job_Loop), Cells(239, Find_The_Job_Loop))
This indicates a range of 210 rows

Rich (BB code):
For Job_Name_Data_Loop = 1 To 200
This indicates a range of 200 rows
 
Upvote 0

The column is dynamic, so it's whatever column is found in Find_The_Job_Loop

The rows that have all of the data in are rows 30 to 239

200 should probably be 209 then i assume?
 
Upvote 0
Thanks for the clarifications. This is how I would approach it. See if it does what you want. You shouldn't need a "Finished" message as with data your size it will be virtually instantaneous.
Nothing is done with the array so you will need to stop the code before it finishes or write the results somewhere to check them.

VBA Code:
Sub Populate_v2()
  Dim Crew_Destination_Array As Variant, Row25Data As Variant, tmpData As Variant
  Dim Find_The_Job_Loop As Long, NextRw As Long, r As Long, myCount As Long
 
  With Sheets("Crew Builder")
    Row25Data = .Rows(25).Resize(, 2000).Value
    For Find_The_Job_Loop = 4 To 2000 Step 32
      If Len(Row25Data(1, Find_The_Job_Loop)) > 0 Then Exit For
    Next Find_The_Job_Loop
 
    If Find_The_Job_Loop < 2000 Then
      myCount = Application.CountA(.Cells(30, Find_The_Job_Loop).Resize(200))
      If myCount > 0 Then
        ReDim Crew_Destination_Array(1 To myCount, 1 To 2)
        tmpData = Application.Index(.Cells, Application.Sequence(200, , 30), Array(Find_The_Job_Loop, 2))
        For r = 1 To UBound(tmpData)
          If Len(tmpData(r, 1)) > 0 Then
            NextRw = NextRw + 1
            Crew_Destination_Array(NextRw, 1) = tmpData(r, 1)
            Crew_Destination_Array(NextRw, 2) = tmpData(r, 2)
          End If
        Next r
      End If
    End If
  End With
End Sub
 
Upvote 0
Solution

I plugged it in & it looks like with a few tweaks it should work. Thank you so much for your assistance!
 
Upvote 0

Forum statistics

Threads
1,226,462
Messages
6,191,177
Members
453,644
Latest member
karlpravin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top