Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! I can't figure this riddle out
I created a spreadsheet inside of which exists a field of 56 data
cells (8x7). These cells track other events that occur throughout the spreadsheet. Each of the 56 cells will usually equal zero, but at any given time, up to 8 of the 56 cells at random will hold a value representing an event that occurred. I need to able to generate a list of those values in the order they occurred. The seven columns of the field represent the days of the week, so the proper order would be a1..a7,b1..b7,etc. The 8 values could be in any of the cells (they may all occur on the same day.) The resulting list would be an 8 cell array that holds the values, if any, in order in the uppermost cells, with the unneeded lower cells blank. Thanks to anyone who can help me solve this riddle. It sounds so simple, but I can't grasp it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! I can't figure this riddle out
Jason: The code looks fine. I'm sure it will work. I'm a self-taught user, and I'm not familiar enough with excel to understand how you interface the code and the spreadsheet. Where do you write it, and how does it link to the actual cells in the "list"? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! I can't figure this riddle out
Do you know how to open up the Visual Basic Editor?
Dave Oste wrote in message ... Jason: The code looks fine. I'm sure it will work. I'm a self-taught user, and I'm not familiar enough with excel to understand how you interface the code and the spreadsheet. Where do you write it, and how does it link to the actual cells in the "list"? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! I can't figure this riddle out
Yes, I've figured out how to record macros, and edit them. I tried pasting your code into a macro named "xxx", and I tried to change some of your code to reflect the actual cells in use, i.e.- the "field" is AE7:AK14, so the y in the loop was "From AE to AK". The results go to a list starting at M39. I didn't know how to set it up without a shortcut, so I just gave it ctrl+S. When I hit the shortcut, I get prompted for a hyperlink?? I may be in over my head here, but I'm learning a ton. Thanks for helping me. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! I can't figure this riddle out
Hi Dave,
Here's an upgrade: Option Explicit Private x As Integer Private y As Integer Private i As Integer Private myValue Private myarray() Private myStart As Object Private myFirstCell As Object Sub xxx() Set myFirstCell = Application.InputBox("Please pick top left hand cell of area to be searched ", Type:=8) For x = 1 To 8 For y = 1 To 7 If myFirstCell(y, x) < "" Then i = i + 1 ReDim Preserve myarray(i) myarray(i) = myFirstCell(y, x) End If Next y Next x Set myStart = Application.InputBox("Please enter start location of Results ", Type:=8) For i = 1 To UBound(myarray) myStart(i, 1) = myarray(i) Next i End Sub NOTE: 1.the line starting "Set myFirstCell =" should finish "to be searched ", Type:=8)" and it all should be on ONE LINE 2.the line starting "Set myStart =" should finish "Type:=8)" and it all should be on ONE LINE This should work where-ever the area is located on the worksheet. Just paste this into a module and run it. A good way to learn is to manipulate the code window so it takes up half the screen and manipulate the worksheet so it fills the other half, select somewhere within the code(below the line "Sub xxx()") and press F8.This should highlight the first line of code in yellow, then press F8 again and only one line of code will be executed, and you'll see what this line has done on the other half of the screen.For example when you press F8 when the followimg line of code is highlighted: Set myFirstCell = Application.InputBox("Please pick top left hand cell of area to be searched ", Type:=8) an inputbox should appear asking you to pick the top left hand cell in the range to be searched - in your example you would use the mouse to pick AE7. Once you have gone past the line "For x = 1 To 8" you are into a loop which will execute 56 times so you may want to press F5 which will execute all the code that remains. I'm not sure about the hyperlink message! atb Jason Dave wrote in message ... Yes, I've figured out how to record macros, and edit them. I tried pasting your code into a macro named "xxx", and I tried to change some of your code to reflect the actual cells in use, i.e.- the "field" is AE7:AK14, so the y in the loop was "From AE to AK". The results go to a list starting at M39. I didn't know how to set it up without a shortcut, so I just gave it ctrl+S. When I hit the shortcut, I get prompted for a hyperlink?? I may be in over my head here, but I'm learning a ton. Thanks for helping me. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XIRR and IRR riddle - Please Help! | Excel Discussion (Misc queries) | |||
Can't figure this out... | Excel Discussion (Misc queries) | |||
HOW TO 'BLINK' A FIGURE INSTEAD OFF BOLDING THE FIGURE | Excel Worksheet Functions | |||
Can't figure out | Excel Worksheet Functions | |||
Still trying to figure it out | Excel Worksheet Functions |