Help! I can't figure this riddle out

  • Thread starter Thread starter guyinatshirt
  • Start date Start date
G

guyinatshirt

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.
 
Try this:

Option Base 1
Option Explicit

Private x As Integer
Private y As Integer
Private i As Integer

Private myValue
Private myarray()
Private myStart As Object
Sub xxx()

For x = 1 To 8
For y = 1 To 7
If Cells(y, x) <> "" Then
i = i + 1
ReDim Preserve myarray(i)
myarray(i) = Cells(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

Does it work? Is it ok?

Jason
 
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"?
 
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.
 
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
 
Back
Top