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.