R/T 9 on Redim Statement line

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,486
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
Workbooks.Add
For i = 1 To UBound(Arr, 1)
For j = 1 To 2
......
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
OK, Just read that First dimemsion cannot be redimmed, ontly the 2nd...

Thanks,

Jim
 
Upvote 0
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
Workbooks.Add
For i = 1 To UBound(Arr, 2)
For j = 1 To 2
ActiveWorkbook.Names.Add Name:=Arr(j, i), RefersTo:=Arr(j, i)
Next j
Next i
End Sub
 
Upvote 0
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
 
    Workbooks.Add
 
    For i = 1 To UBound(Arr, 2)
        ActiveWorkbook.Names.Add Name:=Arr(1, i), RefersTo:=Arr(2, i)
    Next i
 
End Sub
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top