R/T 9 on Redim Statement line

[jim may]

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..

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
......

 

[jim may]

OK, Just read that First dimemsion cannot be redimmed, ontly the 2nd...

Thanks,

Jim

 

[jim may]

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

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

 

[Norie]

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.

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

 

[Marcelo Branco]

Maybe this can help

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.

 

[jim may]

Norie, Marcelo;

Nice code !!

Thanks,

Jim