named range = named ranged

[ndendrinos]

Named range “toplist” is contiguous [A1:L1]
Named range ‘mylist” is non contiguous
(“A24,B23,A16,D20,E12,F27,E16,A15,K14,L20,P21,M19")

On sheet1
I’m looking for code that looks something like this:

[mylist].Value = [toplist].Value

My understanding is that because one range is non contiguous there needs to be a loop
and I need assistance

something like this

Sub test()
Dim rngCell As Range
Dim lngCount As Long
Dim aTemp()

    ReDim aTemp(0)
    lngCount = 0
    
    For Each rngCell In [toplist]
        aTemp(UBound(aTemp)) = rngCell
        ReDim Preserve aTemp(UBound(aTemp) + 1)
    Next rngCell
    
    ReDim Preserve aTemp(UBound(aTemp) - 1)
   


    With Worksheets("sheet1")
        For Each rngCell In [mylist]
        rngCell = aTemp(lngCount)
        lngCount = lngCount + 1
    Next rngCell
End With
End Sub

 

[ndendrinos]

got it to work like this:

Sub hello()
Dim rngCell As Range
Dim lngCount As Long
Dim aTemp()

    ReDim aTemp(1)
    lngCount = 0
    
    For Each rngCell In [toplist]
        aTemp(UBound(aTemp)) = rngCell
        ReDim Preserve aTemp(UBound(aTemp) + 1)
    Next rngCell
    
    ReDim Preserve aTemp(UBound(aTemp) - 0)
   


    With Worksheets("Search")
        For Each rngCell In [mylist]
        rngCell = aTemp(lngCount)
        lngCount = lngCount + 1
    Next rngCell
End With
End Sub

but is there no simplier way?

 

[Richard Schollar]

How about:

Dim i as Long
 
i = 1
 
For Each rngCell In [toplist]
 
  [mylist].Cells(1,i) = rngCell.Value
 
  i = i +1
 
Next

 

[ndendrinos]

Hello Richard ,
Trying your suggestion nothing happens, no error either
Thank you

Just noticed that it does work BUT pastes in range [M26:X26] (and M26 is not selected before firing the code)

 

[Richard Schollar]

Nick

Works fine for me - are both named ranges standard "static" workbook names (ie not worksheet ranges)?

 

[ndendrinos]

Hi Richard not sure what you mean but since I edited my last post I went back and found that I had an extra cell [M26] at the beginning of the named range "my list"
Now what happens is that the code pastes on range [A26:L24]

mylist is: =Search!$A$24,Search!$B$23,Search!$A$16,Search!$D$20,Search!$E$12,Search!$F$27,Search!$E$16,Search!$A$15,Search!$K$14,Search!$L$20,Search!$P$21,Search!$M$19

toplist is: =Search!$A$1:$L$1

 

[ndendrinos]

for anyone following this post having deleted the extra cell in named range "mylist"
I edited the code to this:

Sub hello()
Dim rngCell As Range
Dim lngCount As Long
Dim aTemp()

    ReDim aTemp(0)
    lngCount = 0
    
    For Each rngCell In [toplist]
        aTemp(UBound(aTemp)) = rngCell
        ReDim Preserve aTemp(UBound(aTemp) + 1)
    Next rngCell
    
    ReDim Preserve aTemp(UBound(aTemp) - 1)
   


    With Worksheets("search")
        For Each rngCell In [mylist]
        rngCell = aTemp(lngCount)
        lngCount = lngCount + 1
    Next rngCell
End With
End Sub

Richard's code is way simpler and if it works for him then I must have explained it badly.

 

[Richard Schollar]

Sorry Nik I got confused which way round your named ranges were - try this instead:

Dim i As Long
Dim rngCell As Range

i = 1
 
For Each rngCell In [mylist]
 
  rngCell.Value = Range("toplist")(i)
 
  i = i + 1
 
Next

The above is taking the values in toplist and placing them in mylist. It would have been easier to understand had you not used named ranges!

 

[ndendrinos]

Richard,
Perfect revision to your solution.
On a personal note I would have you know that I am Greek
It is my "raison d'etre" to confuse people.
Have a great day

 

[Richard Schollar]

On a personal note I would have you know that I am Greek
It is my "raison d'etre" to confuse people.
Have a great day

My best friend at work is also Greek and he lives by this ethos too

He's also a joker: when I and another colleague go to his wedding (in Athens) in September, he's already spread rumours among his Greek friends and family that me & this other colleague (another male) are an 'item'!!! I'm going to get him for this though...