How to use name range..Please help to solve this issue..

Solved/Closed
mathewmunna
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
- Apr 27, 2015 at 01:51 AM
 Mathewmunna - May 7, 2015 at 02:19 AM
Hello,

i have a excel work book which has few name ranges eg namerange1, namerange2, namerange3.. how i can display the resultant like if column e1= range1 display namerange range1, e4 = range3 display namerange3 & so on..please help me to solve the issue

2 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
May 4, 2015 at 12:03 PM
Hi Mathew,

Try the following code and see if we are on the right track.
Don't forget to save before running the code, so you can reload your file when some data might be overwritten.

Here is the code:
Sub RunMe()
Sheets("Page1").Select

For Each cell In Range("A1:A5")
    Application.Goto Reference:=cell.Value
    If Sheets("Page2").Range("A1") = vbNullString Then
        Selection.Copy Sheets("Page2").Range("A1")
    Else
        Selection.Copy Sheets("Page2").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
    End If
Next cell

End Sub


Best regards,
Trowa
1
Thank You Very Much.. This is Exactly What i was Looking For.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Apr 30, 2015 at 11:41 AM
Hi Matthew,

So you want to compare a single cell to a range of cells. Per definition they can never be equal. So when will they be equal according to you?

Best regards,
Trowa
0
exactly page1 is input lets consider column A ... i.e a1 to a5 i will give input
like rng1, rng3, rng4, rng2, rng5. i already have named ranges(rng1, rng2..rng5) with different number of columns but equal rows. what i need output is on page2
using the input cells i.e a1= rng1 then start from cell a1.. name range i.e predefined rng1(eg: i defined on page1,G1:J6 as name range i.e "rng1", K1:M6 as "rng2", N1:Q6 as "rng3") output should be on page2 cells A1:D6 show values on "rng1" continuing E1:G6 show values on "rng3" (i.e values on K1:M6, reffering given input on page1 cell a2) and should continue checking till a5 on page1.. Hope u understand my complex reply..

Thanks TrowaD

With regards,
Mathew
0