Pulling multiple arrays from worksheets

Jun 16, 2010
Reaction score
So I've got a very very large database that contains weekly updates for some instrument installation (~24,000 instruments). I've built some code to basically go in, examine cells, and determine what's in them and then write them somewhere else. I've been doing it with loops, and it takes about 20 minutes to run all of the code, seeing as I have to read and write about 200,000 times. I wanted to do this with arrays, and pull in large chunks of data at a time. However, I keep getting various errors.

Here's the code (shortened version)

  Option Base 1
  Option Explicit
  Sub CountStandShort()
  'computes count of stand install dates for week 1, places them into specified week in main sheet.
  Dim weekly As Worksheet
  Dim w1 As Worksheet
  Dim w2 As Worksheet
  Dim w3 As Worksheet
  	'continues to w36 in main code
  Dim n As Double
  Dim stand1 As Double
  Dim stand2 As Double
  Dim stand3 As Double
  	'continues to stand36 in main code
  Set weekly = Worksheets("QUANTITY")
  Set w1 = Worksheets("W1")
  Set w2 = Worksheets("W2")
  Set w3 = Worksheets("W3")
  'initialize variables
  stand1 = 0
  stand2 = 0
  stand3 = 0
  Dim stand1a As Variant
  Dim stand2a As Variant
  Dim stand3a As Variant
  stand1a = w1.Range(Cells(3, 11), Cells(23722, 11))
  stand2a = w2.Range("K3:K23722")
  stand3a = w3.Range("K3:K23722")
  For n = 1 To 23720
  	If stand1a(n) > 1 Then stand1 = stand1 + 1
  	If stand2a(n) > 1 Then stand2 = stand2 + 1
  	If stand3a(n) > 1 Then stand3 = stand3 + 1
  		'this code was originally going to search for date function, that caused errors, see commented code below
  	'If IsDate(stand1a(n)) = True Then stand1 = stand1 + 1
  	'If IsDate(stand2a(n)) = True Then stand2 = stand2 + 1
  	'If IsDate(stand3a(n)) = True Then stand3 = stand3 + 1
  Next n
  weekly.Cells(27, 5) = stand1
  weekly.Cells(27, 6) = stand2
  weekly.Cells(27, 7) = stand3
  End Sub

I've tried playing with ranges and how they are defined, variable types, how the arrays are defined, but I still can't get it to work correctly. Currently I get a "Run Time Error '9': Subscript out of range" error at the line " If stand1a(n) > 1 Then stand1 = stand1 + 1" (right after the big loop starts).

If I declare the arrays as "Dim stand1a() As Variant", I get a type mismatch error when trying to read in the ranges. If I give them a fixed length, such as "Dim stand1a(1 to 23720) As Variant", I get a compile error, can't assign to array.

Any idea what I'm missing in this code?



*edit: changed the range from "K3, K23722" to "K3:K23722" that still gives a mismatch error.
Last edited:


Jun 16, 2010
Reaction score
I figured out the problem, I had to declare the arrays with the empty parenthesis, i.e.

"Dim stand1a() as Variant"

add ".value" to the end of my worksheet range calls, and reference a second dimension in the stand*a arrays, so instead of "stand1a(n)", I had to reference (n, 1). If anyone knows why such things are done, awesome, but it works now.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question