bhsoundman
Board Regular
- Joined
- Jul 17, 2010
- Messages
- 61
- Office Version
- 365
- Platform
- 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!
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