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?

Improve this question
10
  • 1
    What's .Value(1)? 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. Commented Jun 11, 2019 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. Commented Jun 11, 2019 at 18:49
  • 2
    row2Val = row2(1, 1). row2 is a 2D array, as you mention. What are you actually trying to do? This? Commented Jun 11, 2019 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) Commented Jun 11, 2019 at 19:10
  • There's no "array" in row2(1). What do you actually want to do? Commented Jun 11, 2019 at 19:10
Related questions
2
Subscript Out of Range, even though value is defined
1
Subscript out of range in VBA when running this code
0
VBA: Subscript out of range
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.