# R/T 9 on Redim Statement line

#### jim may

##### Well-known Member
I'm getting RT 9 on the RED row below (not the first time thru the loop, but the 2nd time thru) -- a likely reason which at the moment -- I don't know..

Rich (BB code):
``````Sub tester2()
Dim Arr()
Dim i As Long, j As Long
i = 1
j = 1
For Each nm In ActiveWorkbook.Names
ReDim Preserve Arr(1 To i, 1 To 2)
Arr(i, j) = nm.Name
Arr(i, 2) = nm
i = i + 1
Next nm
For i = 1 To UBound(Arr, 1)
For j = 1 To 2
......``````

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### jim may

##### Well-known Member
OK, Just read that First dimemsion cannot be redimmed, ontly the 2nd...

Thanks,

Jim

#### jim may

##### Well-known Member
Now, I'm getting RT 1004 on Line in RED - But Why??

Rich (BB code):
``````Sub tester2()
Dim Arr()
Dim i As Long, j As Long
j = 1
i = 1
For Each nm In ActiveWorkbook.Names
ReDim Preserve Arr(1 To 2, 1 To i)
Arr(j, i) = nm.Name
Arr(j, i) = nm
i = i + 1
Next nm
For i = 1 To UBound(Arr, 2)
For j = 1 To 2
Next j
Next i
End Sub``````

#### Norie

##### Well-known Member
Jim

The 2nd dimension of your array is empty, you need to change the first dimension.

Also, I don't think you need the 2nd variable j.
Code:
``````Option Explicit

Sub tester2()
Dim nm As Name
Dim Arr()
Dim i As Long

For Each nm In ActiveWorkbook.Names
i = i + 1
ReDim Preserve Arr(1 To 2, 1 To i)
Arr(1, i) = nm.Name
Arr(2, i) = nm
Next nm

For i = 1 To UBound(Arr, 2)
Next i

End Sub``````

#### Marcelo Branco

##### MrExcel MVP
Maybe this can help

Code:
``````Sub tester3()
Dim Arr()
Dim i As Long, j As Long
Dim nm As Name
i = 1

ReDim Preserve Arr(ActiveWorkbook.Names.Count, 2)
For Each nm In ActiveWorkbook.Names
Arr(i, 1) = nm.Name
Arr(i, 2) = nm.Value
i = i + 1
Next nm

For i = 1 To UBound(Arr, 1)
For j = 1 To UBound(Arr, 2)
MsgBox Arr(i, j)
Next j
Next i

End Sub``````

M.

Norie, Marcelo;

Nice code !!

Thanks,

Jim

Replies
13
Views
787
Replies
3
Views
397
Replies
6
Views
2K
Replies
0
Views
303
Replies
3
Views
581

1,191,216
Messages
5,985,318
Members
439,956
Latest member
venky2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back