0

I'm trying to access the data from an Excel Range in VBA.

Set fcr = Sheets("FC_Detail").UsedRange
row2 = fcr.Offset(1, 0).Resize(1).Value
row2Val = row2(1)

In this example the Variant row2 should store a 2D array of the 2nd row of the worksheet "FC_Detail" and the Variant row2Val should store the 1D array. (I split into 2 steps to aid debugging.) I can see from the debug window that row2 is exactly what I expect, but I get an error calling row2(1) in my code and in the debug window.

enter image description here

What's weird is that the debug window here says that row2 is a Variant of size (1,45) and that row2(1) is a Variant of size (45). But when I try looking at row2(1) I get an error.

What am I doing wrong?

Scott
  • 3,663
  • 8
  • 33
  • 56
  • 1
    What's `.Value(1)`? [`Range.Value`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.value) does have an optional `RangeValueDataType` argument, but 1 is not a valid option, and it doesn't seem like that's what you're looking for. – BigBen Jun 11 '19 at 18:46
  • .Value returns a 2D array. (1) should provide the first 1D array (row) inside. But in case that syntax confused VBA, I tried the 2nd alternative - row2(1), which also gave me a Subscript Out of Range error. – Scott Jun 11 '19 at 18:49
  • 2
    `row2Val = row2(1, 1)`. `row2` is a 2D array, as you mention. What are you actually trying to do? [This](https://stackoverflow.com/questions/22766184/assign-1d-array-from-2d-array-in-vba)? – BigBen Jun 11 '19 at 18:59
  • row2Val should store a 1D array. Your first solution does not throw an error but it only returns a single cell value. The 2nd solution looks like it's turning a true 2D array into one long 1D array. I'm just trying to access the array inside row2(1) – Scott Jun 11 '19 at 19:10
  • There's no "array" in `row2(1)`. What do you actually want to do? – BigBen Jun 11 '19 at 19:10
  • The debug screen shows me a Variant of size 45 in row2(1). Is that not how arrays work in VBA? – Scott Jun 11 '19 at 19:11
  • Why are you resizing to 1? – SJR Jun 11 '19 at 19:14
  • If I can suggest a read, Chip Pearson on [arrays and ranges](http://www.cpearson.com/excel/ArraysAndRanges.aspx) is good. I don't see why you need to convert to a 1D array. – BigBen Jun 11 '19 at 19:14
  • I guess I can work around it so I don't *need* it. Would like to understand why I can't access the thing I see in Debug. I'm actually porting an Apps Script code that says: var row2 = ss.getSheetByName("FC_Detail").getRange("2:2").getValues()[0]; – Scott Jun 11 '19 at 19:20
  • If you want to iterate, `Dim i as Long`, then `For i = LBound(row2, 2) to UBound(row2, 2)`. If you want to convert to a 1D array, the linked question mentioned earlier should work. – BigBen Jun 11 '19 at 19:25

0 Answers0