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
 
@Peter_SSs, I think you might have missed this:
Thanks Alex. As it turns out I hadn't missed that but just posted the wrong version of my code :oops:

@bhsoundman
The v3 code I meant to post was this, though I'm thinking that you have already worked this part out. :biggrin:

Rich (BB code):
Sub Populate_v3()
  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(210))
      If myCount > 0 Then
        ReDim Crew_Destination_Array(1 To myCount, 1 To 2)
        tmpData = Application.Index(.Cells, Application.Sequence(210, , 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


Thank you so much for your assistance!
You are welcome. :)
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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