Pulling multiple arrays from worksheets

[rskelton]

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
  
  'may
  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?

Thanks,

-cole

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

 

[rskelton]

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.

thanks,

-cole