1

Trying to copy a large range of values into an array to filter the data for analysis. Getting the following error on the 8th line of code after dim statements when I step through the code w/ F8. Thanks in advance for your help!

Dim raw_data() As Variant, rc_fdata() As Variant, src_fdata() As Variant, fc_fdata() As Variant, efc_fdata() As Variant
Dim last_entry As Long, lr As Long, lc As Long
Dim rc_s As Single, src_s As Single, fc_s As Single, efc_s As Single
Dim rr As Range, rc_p As Range
Dim w As Integer, x As Integer, y As Integer, z As Integer, c As Integer, r As Integer
w = 1                                               'initialize array column counters
x = 1
y = 1
z = 1
lr = Sheet2.Range("D4").SpecialCells(xlCellTypeLastCell).Row     'holds last row
lc = Sheet2.Range("D4").SpecialCells(xlCellTypeLastCell).Column  'holds last column
ReDim raw_data(1 To lr, 1 To lc)
raw_data = Sheet2.Range(Cells(3, 5), Cells(lr, lc)) 'placing raw data in array

,

kate
  • 11
  • 1
  • 2
    Does this answer your question? [Why does Range work, but not Cells?](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) – BigBen Mar 09 '20 at 14:23
  • Side note: give [this](http://www.cpearson.com/excel/ArraysAndRanges.aspx) a read. I think you're overcomplicating reading a range into an array. – BigBen Mar 09 '20 at 14:33
  • You need to qualify the worksheet that the `Cells` are on. `Sheet2.Cells(3, 5)`, `Sheet2.Cells(lr, lc)`. – BigBen Mar 09 '20 at 14:44
  • Your array size doesn't match the size of the range either btw. That's why I suggested the second link. No `ReDim` needed. – BigBen Mar 09 '20 at 14:45
  • Can you post your code after the changes you have applied based on @BigBen response? Also, if you need to filter data, why not use the built in functionality of filtering the data on a sheet (can be performed with VBA.. if you really need to use VBA). I suspect it might be quicker – Zac Mar 09 '20 at 14:45
  • 1
    @BigBen fixed using a with statement and qualifying what worksheet the cells were on! thanks – kate Mar 09 '20 at 15:01

0 Answers0