Find select values on one worksheet and copy to a second In the Calculator worksheet of my spreadsheet, for any appliance type present in a building being studied, users enter a quantity. There are 18 different appliance types, and users can enter different quantities of any or all appliance types.

On the Calculator sheet they can see a breakdown of energy use per individual appliance, per appliance type, and for all appliances with a quantity entered:



The macro below (run from a second sheet) copies specific values from Calculator so that they can be pasted into another spreadsheet which is used for reporting. It selects only appliance types for which the user entered a quantity.



Sub findAppliances()

    ' Get Range
    Dim rg As Range
    Set rg = ThisWorkbook.Worksheets("Calculator").Range("F5:F22")

    ' Create dynamic array
    Dim ApplianceQty As Variant

    ' Read values into array
    ApplianceQty = rg.Value

    ' Manipulate and copy the appropriate values
    Dim i As Long, j As Long
    j = 4
    Dim target As String
    Dim source As String
    For i = LBound(ApplianceQty) To UBound(ApplianceQty)
        target = j
        If ApplianceQty(i, 1) &lt;&gt; "" Then
            source = i + 4
            Range("B" &amp; target) = Worksheets("Calculator").Range("F" &amp; source) &amp; " units"
            Range("F" &amp; target) = "=F" &amp; target - 1 &amp; "-" &amp; Worksheets("Calculator").Range("J" &amp; source)
            Range("G" &amp; target) = "=G" &amp; target - 1 &amp; "-" &amp; Worksheets("Calculator").Range("K" &amp; source)
            Range("H" &amp; target) = "=H" &amp; target - 1 &amp; "-" &amp; Worksheets("Calculator").Range("L" &amp; source)
            Range("K" &amp; target) = Worksheets("Calculator").Range("E" &amp; source)
            Range("L" &amp; target) = Worksheets("Calculator").Range("C" &amp; source)
            j = j + 1
        End If
    Next i

    ' Select and copy the text for export
    Range("B4:M" &amp; target).WrapText = False
    Range("B4:M" &amp; target).Select
    Range("B4:M" &amp; target).Copy

End Sub


I suspect that the reason this takes so long to run is the group of six Range() = Worksheets("Calculator").Range() lines within my For loop. I further suspect that if I read all the data I need into an array, then printed it back out of the array (rather than reading cells and copying to new cells), the whole thing would run faster.

Is that true? How much improvement can I expect?
First - nice job declaring all of your variables. The naming could use a little work (sourceRange, applianceQuantityArray). Declaring several on the same line and giving them both a type (below) - A+


Dim i As Long, j As Long



A few standard blurbs -


  Always turn on Option Explicit. You can have it automatically by
  going to Tools -> Options in the VBE and checking the Require
  Variable Declaration option. This way if you have any variables not
  defined, the compiler will let you know.
  
  Worksheets have a CodeName property - View Properties window
  (F4) and the (Name) field (the one at the top) can be
  used as the worksheet name. This way you can avoid Sheets("mySheet")
  and instead just use mySheet.
  
  Standard VBA naming
  conventions
  have camelCase for local variables and PascalCase for other
  variables and names.




I do want to talk about a few variables though-

Dim j as Long
Dim target as String
Dim i as Long
Dim source as String
j=4
&gt;loop i
   target=j
   source = i+4
   j=j+1
&gt;end loop


This seems pretty unnecessary. I'll take this -


 Range("B" &amp; target) = Worksheets("Calculator").Range("F" &amp; source) &amp; " units"



to mean that you thought you need to use a string to put into a range format. You don't. This does the same thing (i being arbitrary) -

Dim i As Long
For i = 1 To 10
    Sheet1.Range("B" &amp; i + 3) = Sheet1.Range("F" &amp; i + 4).value &amp; " units"
Next


You'd probably benefit from just using the .Cells(row, col) format instead. But I digress.



Using an array

As you yourself have pointed out, using an array would be faster. It's always faster. Your loop is really difficult to read -


Range("H" &amp; target) = "=H" &amp; target - 1 &amp; "-" &amp; Worksheets("Calculator").Range("L" &amp; source)



(You're subtracting 1 from a String you realize?) It's the same as this -

 Sheet1.Cells(target, 8).value = Sheet1.Cells(target - 1, 8) - calcSheet.Cells(source, 12)


In the most basic way, say you take everything you need into an array

Dim calcArray As Variant
calcArray = calcsheet.Range(calcsheet.Cells(5, 3), calcsheet.Cells(22, 13))
For i = LBound(calcArray) To UBound(calcArray)
    If calcArray(i, 4) &lt;&gt; vbNullString Then


Pretty simple yeah? Just use the 2nd dimension of the variant to specify what column you're targeting (see I started at column 3 (C) so I had to use 4 (F).

Looks to me like you would need an array from the other sheet too, something like

Dim targetArray As Variant
targetArray = Sheet1.Range(Sheet1.Cells(4, 6),Sheet1.Cells(UBound(calcArray) - 1, 9))


And then maybe you make a results array

Dim resultsArray As Variant
ReDim resultsArray(1 To UBound(calcArray), 1 To 3)


Now you can (roughly, this isn't perfect)

For i = LBound(calcArray) To UBound(calcArray)
    If calcArray(i, 4) &lt;&gt; vbNullString Then
        resultsArray(i, 1) = targetArray(i - 1) - calcArray(i, 8)


Populate that results array using what is essentially range("F5").offset(,-1) but in the array.

Then it would need to be printed back out. A few of them come straight from the calcArray hence I didn't make room in the resultsArray but you could.

Anyway, since the range is weird to me you'd just need to adjust it as you like. Or just declare your arrays starting in row 1 and change nothing.