Find select values on one worksheet and copy to a second
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.