Solved

Unsolvable - Displaying Named Ranges based on Clicking a Cell

Posted on 2004-08-11
5
131 Views
Last Modified: 2010-05-02
EE,
 
How do I move only the names into a another sheet but I want them to be in a 3 columns instead of one long column?
And then when you click on a name, it will display it's range a few columns over.  Picture this.. a list of names and when you click on the name, all of the person's data is displayed, which IS already defined.  I have the named ranges defined (insert menu > names > define). Any sample code on how to do this?


     A          ..       J
John Smith        data
                        data
                        data
Jane Doe           data
Mike K. Jordan   data
                        data
                        data
                        data
   .
   .
   .
<<Points refunded (500) because user answered question himself
byundt--Excel TA Page Editor>>
0
Comment
Question by:efarhat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:efarhat
ID: 11779150
i'll figure out how to add the color later. have to make it aesthetically pleasing.
0
 

Author Comment

by:efarhat
ID: 11782895
can anyone please provide feedback on this? I am really struggling.
0
 

Author Comment

by:efarhat
ID: 11784145
right now this is what i have displaying a list of names, except it skips the first name and i haven't been able to figure out the associate of ranges...

i also figured it would be easier to display 5 columsn of 10 names in each.


Sub groupTraderNamesInNewReport()
Dim r, c, d As Range, sTraderNames As String
Dim i As Long
i = 0
    Set r = Sheets("layout").Range("A4")
    Set d = Sheets("report").Range("A1")
     
   While Not (IsEmpty(r) And IsEmpty(r.Offset(0, 1)))
        Set c = r.Offset(1, 0)
        While IsEmpty(c) And (Not IsEmpty(c.Offset(0, 1)))
            Set c = c.Offset(1, 0)
            If c.Offset(-1, 0).value = r.Offset(0, 0).value Then d.Offset(i, 0).value = c.value
        Wend
        d.Offset(i, 0).value = c.value
        Set r = c
        i = i + 1
    Wend


End Sub


0
 

Accepted Solution

by:
efarhat earned 0 total points
ID: 11785704
ok, i was able to generate the columns:

Sub MoveTraders()

    Dim Trader As Range
    Dim Traders As Range
    Dim ColCount As Integer
   
    Sheets("Report").Select
    Range("A1").Select
   
    Set Traders = Range(ActiveCell, ActiveCell.End(xlDown))
   
    Sheets("Layout").Select
    Range("A1").Select
   
    ColCount = 1
    For Each Trader In Traders
        If ColCount > 3 Then  ' set this value to the number of columns you require
            ColCount = 1
            ActiveCell.Offset(1, 0).Select
        End If
        ActiveCell.Offset(0, (ColCount - 1) * 2).Value = Trader
        ColCount = ColCount + 1
    Next Trader
   
   
End Sub



BUT: how do i associate a Traders name with a defined region?  and for it to display when i click on the name...
0
 
LVL 81

Expert Comment

by:byundt
ID: 11817095
Essa,
I am going to refund your points on this question since you have solved the first part yourself, and have asked the second part in http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21091484.html

I also notice that you have asked another question in two technical areas. Experts-Exchange policy is for you to ask a question in only one forum. That said, you can use Pointer questions to alert experts in other technical areas about your problem. The title of this question might be "50 point question on "Object defined or application defined error in Excel VBA". The body of the question would then describe a few more details and ask experts in the Visual Basic TA to post comments in the following link:
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21095236.html

I have converted this link into a Pointer question: http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21095174.html

byundt--Excel TA Page Editor
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question