Any help appreciated on this one.
I'm dynamically creating named ranges in Excel VBA code:
index.rangeName = idxRngPrefix & Replace(index.mdSymbol, ".", "_")
ActiveWorkbook.Names.Add index.rangeName, Range(Worksheets("index compositions").Range("A8").Offset(0, i * indexColumnOffset).Address, Worksheets("index compositions").Range("M8").Offset(j + 5, i * indexColumnOffset).Address).Address
I can see that these are being generated correctly by checking the defined names list in Excel.
However, when I try and refer to the ranges I get an error:
Run-time error '1004'
Method 'Range' of object '_Global' failed
The code that I am referring to the ranges in is:
For Each index In idxs
For i = 1 To index.components.count
For Each component In index.components
If Range(index.rangeName).Item(6 + i, 1) = component.id Then
component.price = Range(index.rangeName).Item(6 + i, 1)
Exit For
End If
Next
Next
Next
I have tried prefixing "Range" with "Activeworkbook", but this does not help.
Thanks for looking.
Sunkai
I'm dynamically creating named ranges in Excel VBA code:
index.rangeName = idxRngPrefix & Replace(index.mdSymbol, ".", "_")
ActiveWorkbook.Names.Add index.rangeName, Range(Worksheets("index compositions").Range("A8").Offset(0, i * indexColumnOffset).Address, Worksheets("index compositions").Range("M8").Offset(j + 5, i * indexColumnOffset).Address).Address
I can see that these are being generated correctly by checking the defined names list in Excel.
However, when I try and refer to the ranges I get an error:
Run-time error '1004'
Method 'Range' of object '_Global' failed
The code that I am referring to the ranges in is:
For Each index In idxs
For i = 1 To index.components.count
For Each component In index.components
If Range(index.rangeName).Item(6 + i, 1) = component.id Then
component.price = Range(index.rangeName).Item(6 + i, 1)
Exit For
End If
Next
Next
Next
I have tried prefixing "Range" with "Activeworkbook", but this does not help.
Thanks for looking.
Sunkai