Value not stored in Dynamic Array

Question

Earlier it works fine but now the data is not store in array and the same is store in rng1 I can check in dubug windows by ?rng1(1,1) or ?rng1(39,4) but vArray is giving error subscript out range.

Dim vArray() As Variant
Dim rng1 As Range
Dim ShNm As String
Dim lROW AS LONG 

ShNm = Sheets("Main").Range("G3")

lRow = Worksheets(ShNm).Cells(Rows.Count, 1).End(xlUp).Row 

Set rng1 = Worksheets(ShNm).Range("A1:D" & lRow)

vArray = Worksheets(ShNm).Range("A1:D" & lRow)
or        
vArray = Worksheets(Sheets("Main").Range("G3")).Range("A1:D39")
or
vArray = Worksheets("ListofSheets").Range("A1:D39")

Ralph · Accepted Answer · 2016-04-20 14:14:10Z

1

Try this:

vArray = Worksheets("ListofSheets").Range("A1:D39").Value2

answered Apr 20, 2016 at 14:14

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

curious K Over a year ago

It works!...Thanks! but what is value2 syntax where I can get help or more explanation on this.

Ralph Over a year ago

If you have questions in Excel VBA about something just highlight the word you are looking help on (in this case, highlight Value2) and then press F1.

Ralph Over a year ago

.Value will probably also work. .Value2 is just faster than .Value. Therefore I prefer it and if you are not dealing with dates or currencies then there is no difference.

Collectives™ on Stack Overflow

Value not stored in Dynamic Array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related