efarhat
asked on
Unsolvable - Displaying Named Ranges based on Clicking a Cell
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>>
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>>
ASKER
can anyone please provide feedback on this? I am really struggling.
ASKER
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 groupTraderNamesInNewRepor
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
i also figured it would be easier to display 5 columsn of 10 names in each.
Sub groupTraderNamesInNewRepor
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 https://www.experts-exchange.com/questions/21091484/DIFFICULT-Q-Move-Data-and-Named-Ranges-to-another-sheet.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:
https://www.experts-exchange.com/questions/21095236/help-Application-defined-or-object-defined-error.html
I have converted this link into a Pointer question: https://www.experts-exchange.com/questions/21095174/50-point-question-on-Excel-VBA-Application-defined-or-object-defined-error.html
byundt--Excel TA Page Editor
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 https://www.experts-exchange.com/questions/21091484/DIFFICULT-Q-Move-Data-and-Named-Ranges-to-another-sheet.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:
https://www.experts-exchange.com/questions/21095236/help-Application-defined-or-object-defined-error.html
I have converted this link into a Pointer question: https://www.experts-exchange.com/questions/21095174/50-point-question-on-Excel-VBA-Application-defined-or-object-defined-error.html
byundt--Excel TA Page Editor
ASKER