Write shorter For Loop for Array

Question

I wrote the following For Loop to copy and paste several ranges from one sheet to another. I used an array. The procedure works fine, but I was looking for a shorter way to do the same.

Dim copyRange(1 To 3) As String
Dim pasteRange(1 To 3) As String
Dim refRange(1 To 3) As String
Dim i As Long

copyRange(1) = "A5"
copyRange(2) = "G5"
copyRange(3) = "H5"

refRange(1) = "A"
refRange(2) = "G"
refRange(3) = "H"

pasteRange(1) = "BE3"
pasteRange(2) = "CA2"
pasteRange(3) = "CD2"

For i = 1 To 3

         refApplicantFlow.Range(copyRange(i), refApplicantFlow.Range(refRange(i) & Rows.Count).End(xlUp)).Copy _
            calcCalculations.Range(pasteRange(i))

Next i

your loop is 1 line of code, how many lines should be in shorter code? — Iłya Bursov
– Iłya Bursov, Commented Aug 19, 2014 at 16:26
Is there a reason you cannot use the Range(target(i)).Value = Range(source(i)).Value syntax? Where target and source are arrays of strings that identify the range(s) to copy values to and from? — JustinJDavies
– JustinJDavies, Commented Aug 19, 2014 at 16:27
There are only 3 ranges being copied, and most of the lines are just setup for the loop. Do you need to use a loop instead of just 3 lines doing the copying? — MRAB
– MRAB, Commented Aug 19, 2014 at 16:34

Peter Albert · Accepted Answer · 2014-08-19 20:36:24Z

2

First of all, I would strongly recommend, to store the references to the cells as hard coded strings in your macro. The moment your workbook's structure is slightly adjusted, your macros will fail/copy the wrong things!

Instead, use named ranges. I.e. click on A5 and assign the name Source_1 or so to it. G5 will become Source_2, H5 Source_1, G5 Target_1, etc.

The use this code:

lngRows = Rows.Count
For i = 1 To 3
   Range("Target_"&i).Resize(lngRows).Value = Range("Source_"&i).Resize(lngRows).Value
Next

This way, your macro will still work, even if the workbook structure changes! And your line of code became shorter! ;-)

edited Aug 19, 2014 at 20:36

answered Aug 19, 2014 at 19:43

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ross McConeghy · Accepted Answer · 2014-08-19 18:24:23Z

0

Your macro is pretty efficient if the intent was to include a loop and arrays. The same thing could be accomplished in 3 lines:

refApplicantFlow.Range("A5", refApplicantFlow.Range("A" & Rows.Count).End(xlUp)).Copy _
calcCalculations.Range("BE3")
refApplicantFlow.Range("G5", refApplicantFlow.Range("G" & Rows.Count).End(xlUp)).Copy _
calcCalculations.Range("CA2")
refApplicantFlow.Range("H5", refApplicantFlow.Range("H" & Rows.Count).End(xlUp)).Copy _
calcCalculations.Range("CD2")

answered Aug 19, 2014 at 18:24

Ross McConeghy

8742 gold badges7 silver badges16 bronze badges

Collectives™ on Stack Overflow

Write shorter For Loop for Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related