Condensing code - Using Arrays for Loops

[sllaksvb]

Hi all,

I am trying to get my macro to run faster, and it is probably due to too many loops. I am trying to use Arrays but my understanding of Arrays are pretty limited. It would be great if someone could help me with condensing my code using Arrays so that I could learn how to implement it through my macro.

        Dim fbasic2(2 To 16)
                 Dim a As Long
        For a = 2 To UBound(fbasic2)
            With Sheets("Sheet1")
                .Cells(a, 5).Copy _
                    Destination:=Sheets("DETAILS").Cells(a + 1, 8)
                .Cells(a, 10).Copy _
                    Destination:=Sheets("DETAILS").Cells(a + 1, 16)
                .Cells(a, 13).Copy _
                    Destination:=Sheets("DETAILS").Cells(a + 1, 21)
            End With
        Next a

My macro consists of many loops like the one above. I am able to use an array for numbers if sequence, but unable to do the same for the columns which do not go in sequential order.

Any help would be greatly appreciated and would boost my understanding of arrays! Thank you.

[snb]

something to read: http://www.snb-vba.eu/VBA_Arrays_en.html

[Paul_Hossler]

I don't see where / how you're using the array in the 2 to 16 loop

Can't you just copy each of the 3 blocks of 15 cells all at once?

    With Sheets("Sheet1")
        .Cells(2, 5).Resize(15, 1).Copy Sheets("DETAILS").Cells(3, 8)
        .Cells(2, 10).Resize(15, 1).Copy Sheets("DETAILS").Cells(3, 16)
        .Cells(2, 13).Resize(15, 1).Copy Sheets("DETAILS").Cells(3, 21)
    End With

If you were going to manipulate the input data, there might be some benefit to using arrays

[sllaksvb]

I'm sorry Paul, I posted the wrong code.

Dim dependencies(3 To 17)
    Dim depcol(1 To 10)
    Dim j As Long
    depcol(1) = 5
    depcol(2) = 7
    depcol(3) = 30
    depcol(4) = 42
    depcol(5) = 43
    depcol(6) = 44
    depcol(7) = 53
    depcol(8) = 46
    depcol(9) = 40
       For x = LBound(dependencies) To UBound(dependencies)
       For j = LBound(depcol) To UBound(depcol)
    Set rng = Sheets("DETAILS").Cells(x, 1)
    For Each cell In rng
        If cell.Value <> "" Then
            With Sheets("Sheet1")
                .Cells(17, 3).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 5)
                .Cells(25, 3).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 7)
                .Cells(50, 3).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 42)
                .Cells(52, 3).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 43)
                .Cells(28, 9).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 44)
                .Cells(52, 9).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 53)
                .Cells(19, 3).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 30)
                .Cells(43, 9).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 40)
                .Cells(36, 3).Copy _
                    Destination:=Sheets("DETAILS").Cells(x, 46)
            End With
            Sheets("DETAILS").Cells(x, 15).Value = "New F"
        ElseIf cell.Value = "" Then
            With Sheets("DETAILS")
                .Cells(x, j).Value = ""
            End With
        End If
    Next
    Next
    Next

However, after reading your version of the other code, should I be using the copy function like you did before on this code?

[Paul_Hossler]

snb gave you a great link to using arrays in post #2

Comments:


1. You never use 'dependences' as an array, only as a loop index. You can just use

For x = 3 To 17

2. You put values into 'depcol'

depcol(1) = 5 
depcol(2) = 7 
depcol(3) = 30 
depcol(4) = 42 
depcol(5) = 43 
depcol(6) = 44 
depcol(7) = 53 
depcol(8) = 46 
depcol(9) = 40

But never use the array. You probably wanted something like

 .Cells(17, 3).Copy Destination:=Sheets("DETAILS").Cells(x, depcol(x-2))

with the -2 because x starts are 3 from 'dependences' while 'depcol' starts at 1


3. Most start at column C, but 3 start at Col I - could be correct, just different

.Cells(28, 9).Copy Destination:=Sheets("DETAILS").Cells(x, 44)
.Cells(52, 9).Copy Destination:=Sheets("DETAILS").Cells(x, 53)
.Cells(19, 3).Copy Destination:=Sheets("DETAILS").Cells(x, 30)
.Cells(43, 9).Copy Destination:=Sheets("DETAILS").Cells(x, 40)

4. If it's not blank, then it must be blank so just an 'Else' would be sufficient

            If cell.Value <> "" Then
            
             Else
'            ElseIf cell.Value = "" Then

            End If

5. Since Rng only contains a single cell, the 'For Each' really doesn't add much

        Set Rng = Sheets("DETAILS").Cells(x, 1)
        For Each cell In Rng

6. Your new version only copies a single cell value, but the original copied 15 rows, single column. Copying a block of 15 is faster than copying a cell 15 times


7. This is just an example of using arrays. It's not the other approach to using arrays in which WS data is brought into a VBA array, manipulated, and then put back out:

Option Explicit

Sub test2()

    Dim dependencies As Variant, depcol As Variant
    Dim  x As Long

    dependencies = Array(17, 25, 50, 52, 28, 52, 19, 43, 36, 3, 3, 3, 3, 9, 9, 3, 9, 3) '   0 to 17
    depcol = Array(5, 7, 30, 42, 43, 44, 53, 46, 40)    '   0 to 8
    
    For x = LBound(depcol) To UBound(depcol)
        If Len(Sheets("DETAILS").Cells(depcol(x), 5).Value) > 0 Then
            Sheets("Sheet1").Cells(dependencies(x), dependencies(x + 9)).Copy _
                Sheets("DETAILS").Cells(x + 3, depcol(x))
        Else
            Sheets("DETAILS").Cells(x + 3, depcol(x)).ClearContents
        End If
    Next x
    
End Sub

[sllaksvb]

Paul, thank you for your prompt reply and detailed explanation and examples. This really helped.
I'll also check out snb's link on arrays. Thank you.