Nested For Next Loop not working

[Dman333]

Hello everyone - I'm trying to get a nested for next loop to work. The inside loop one pulls some data based on dates, cost codes and puts it into a sheet. But I need to do this with up to 4 BusUnits at a time. The form that drives this and the inside loop work, its just when I tried to get an outside loop to go through 4 BusUnits, and particularly the attempt to use the variant in the If Then formula. I feel like I'm pretty close and any help is greatly appreciated.

Thanks



Dim arrbu() As Variant
Dim arrcc() As Variant
Dim bu As Variant
Dim BusUnit As Variant
Dim Ccode As Variant

arrbu = Array("Busunit1", "Busunit2", "Busunit3", "Busunit4")
arrcc = Array("ccode1", "ccode2", "ccode3", "ccode4")


For Each bu In arrbu

BusUnit = arrbu
Ccode = arrcc

For X = 5 To 10000 'wslr
If TExportws.Cells(X, 21) >= ws.Range("StartDate") And _
TExportws.Cells(X, 21) <= ws.Range("EndDate") And _
TExportws.Cells(X, 6) = Ccode And _
TExportws.Cells(X, 25) = BusUnit Then

ws.Cells(Row, 17) = Val(TExportws.Cells(X, 25)) 'Bus Unit Number
ws.Cells(Row, 18) = TExportws.Cells(X, 6) 'Cost Code
ws.Cells(Row, 19) = TExportws.Cells(X, 7) 'Description
ws.Cells(Row, 20) = TExportws.Cells(X, 8) 'Old Value
ws.Cells(Row, 21) = TExportws.Cells(X, 9) 'New Value
ws.Cells(Row, 22) = TExportws.Cells(X, 10) 'Previous Budget - Line Item
ws.Cells(Row, 23) = TExportws.Cells(X, 11) 'Revised Budget - Line Item
ws.Cells(Row, 24) = TExportws.Cells(X, 12) 'Increase / (Decrease) - Line Item Budget
ws.Cells(Row, 25) = TExportws.Cells(X, 14) 'Previous Budget - Cost Code
ws.Cells(Row, 26) = TExportws.Cells(X, 15) 'Revised Budget - Cost Code
ws.Cells(Row, 27) = TExportws.Cells(X, 16) 'Increase / (Decrease) - Cost Code Budget
ws.Cells(Row, 28) = TExportws.Cells(X, 13) 'Notes

ws.Cells(Row, 17).NumberFormat = "General"
ws.Cells(Row, 18).NumberFormat = "General"
ws.Cells(Row, 20).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
ws.Cells(Row, 21).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
ws.Cells(Row, 22).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
ws.Cells(Row, 23).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
ws.Cells(Row, 24).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
ws.Cells(Row, 25).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
ws.Cells(Row, 26).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
ws.Cells(Row, 27).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

Row = Row + 1

End If
Next X
Next bu

 

[Yongle]

When posting code, remember to post it inside code tags - by clicking on <vba/> or <rich/> which makes it easier to read (look at the code below and compare it to post#1)

These 2 lines in your code look wrong to me - both arrbu and arrcc are arrays of values - you are wanting to use the individual values
BusUnit = arrbu
Ccode = arrcc

It is not clear to me exactly how you want to loop, but this may help you get there
- it illustrates how to get the individual values
Run macro below and look at the Immediate Window to see what is produced
- display Immediate Window in VBA editor with {CTRL} g

Sub BusUnits()
    Dim arrbu() As Variant
    Dim arrcc() As Variant
    Dim bu As Variant
    Dim BusUnit As Variant
    Dim Ccode As Variant
   
    arrbu = Array("Busunit1", "Busunit2", "Busunit3", "Busunit4")
    arrcc = Array("ccode1", "ccode2", "ccode3", "ccode4")

    For Each bu In arrbu
        For Each Ccode In arrcc
            Debug.Print bu, Ccode
        Next Ccode
    Next bu
End Sub

 

[Dman333]

Thank you so much! I'm pretty new to VBA and this helped get me where I needed to go. I changed my approach and used a range instead of the array and that got the loop to work the way
I needed.
Very much appreciated!
Cheers