Excel VBA Array to non-contiguous named ranges

Question

I'm trying to get data from an array into a non-contiguous named range. I keep ending up with the first cell of data is all of the named range cells. I think I hurt my brain. I'm looking at my code and not even sure why I wrote it, so I'll just leave that part out.

I need data copied from A2:I2 then pasted into named range "blah" (B2,D2,F2,B4,D4,F4,B6,D6,F6)

Can someone point me in the correct direction please? It's actually way more complicated than this, but I can't seem to get this part working. Thanks for any insight.

Could you share the piece of code that produces unexpected results? — Bernard Saucier
– Bernard Saucier, Commented Aug 12, 2014 at 0:09

Gary's Student · Accepted Answer · 2014-08-12 00:33:10Z

1

Consider this as an example:

Sub MoveData()
    Dim r1 As Range, r2 As Range, r As Range, _
        indx As Long, ary()
    Set r1 = Range("A2:I2")
    Set r2 = Range("B2,D2,F2,B4,D4,F4,B6,D6,F6")
    r2.Name = "blah"

    ary = r1

    indx = 1
    For Each r In Range("blah")
        r.Value = ary(1, indx)
        indx = indx + 1
    Next r
End Sub

answered Aug 12, 2014 at 0:33

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Greg Over a year ago

That works perfectly. Was hoping to do it by the range names and keep it dynamic instead of hard coding the cell locations, but this will get the job done and I need to be finished with this project. Thank you for your help!

risk_xlr8 · Accepted Answer · 2014-08-12 01:46:53Z

0

There are multiple ways of referencing cells in excel cell these are relative or absolute or semi-absolute. Have you tried using another style.

Or

can you use an offset function like this in the next column =OFFSET($a2,0,2*(row()-1))

answered Aug 12, 2014 at 1:46

risk_xlr8

11 bronze badge

Collectives™ on Stack Overflow

Excel VBA Array to non-contiguous named ranges

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related