I have a spreadsheet that I'm trying to set up that keeps tack of some sale
figures (all visible on one screen) in sets of 5 columms with each set
containing 4 cells on each row (Amt, Date, By, Note):
Set 1 (Colums A-D): Amt (Col A), Date (Col B), By (Col C), Note (Col D) for
each transaction. 27 items in rows 5 - 31
Set 2 (Column E-H): Amt (Col E), Date (Col F), By (Col G), Note (Col H) for
each transaction. 27 items in rows 5 - 31
Set 3 (Column I-L): Amt (Col I), Date (Col J), By (Col K), Note (Col L) for
each transaction. 27 items in rows 5 - 31
Set 4 (Column M-P): Amt (Col M), Date (Col N), By (Col O), Note (Col P) for
each transaction. 27 items in rows 5 - 31
Set 5 (Column Q-T): Amt (Col Q), Date (Col R), By (Col S), Note (Col T) for
each transaction. 27 items in rows 5 - 31

Now, what I want to do is to have Excel start by going down Colum A (for the
first group) (starting at Row 5) and find the first empty cell. If it
reaches the last cell in that column (A31) and it has data in it, I want it
to go to cell E5 (the start of the 2nd group of columns) and do the same
thing there... only stopping when it hits an empty cell. This would
continue through all 5 sets of data (if necessary) until it finds the first
empty cell in the Amt column.
Thanks for any help I can get.

Jonco

Good Afternoon Jonco,
Here are two marco subs that get the same results. The only difference in
the two subs is, FindEmpty2() breaks range down into Areas ie each of your
section to search for the empty cells The other FindEmpty1(), treats the
search as on big range.. The results is still the same....

enjoy, Rick, FBKS, AK


Sub FindEmpty2()
Dim Srng As Range, Arng As Range
Dim FoundCell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set Srng = ws.Range("A5:A32,E5:E32,I5:I32,M5:M32,Q5:Q32")

For Each Arng In Srng.Areas

Set FoundCell = Arng.Find(what:="", LookIn:=xlFormulas,
searchorder:=xlByRows)
If Not FoundCell Is Nothing Then
FoundCell.Select '' select empty cell
Exit Sub
End If
Next Arng

End Sub

================================================== ================
Sub FindEmpty1()
Dim Srng As Range
Dim FoundCell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set Srng = ws.Range("A5:A32,E5:E32,I5:I32,M5:M32,Q5:Q32")

Set FoundCell = Srng.Find(what:="", LookIn:=xlFormulas,
searchorder:=xlByRows)
If Not FoundCell Is Nothing Then
FoundCell.Select '' select empty cell
Exit Sub
End If

End Sub



"jonco" wrote in message
. com...
I have a spreadsheet that I'm trying to set up that keeps tack of some
sale
figures (all visible on one screen) in sets of 5 columms with each set
containing 4 cells on each row (Amt, Date, By, Note):
Set 1 (Colums A-D): Amt (Col A), Date (Col B), By (Col C), Note (Col D)
for
each transaction. 27 items in rows 5 - 31
Set 2 (Column E-H): Amt (Col E), Date (Col F), By (Col G), Note (Col H)
for
each transaction. 27 items in rows 5 - 31
Set 3 (Column I-L): Amt (Col I), Date (Col J), By (Col K), Note (Col L)
for
each transaction. 27 items in rows 5 - 31
Set 4 (Column M-P): Amt (Col M), Date (Col N), By (Col O), Note (Col P)
for
each transaction. 27 items in rows 5 - 31
Set 5 (Column Q-T): Amt (Col Q), Date (Col R), By (Col S), Note (Col T)
for
each transaction. 27 items in rows 5 - 31

Now, what I want to do is to have Excel start by going down Colum A (for
the
first group) (starting at Row 5) and find the first empty cell. If it
reaches the last cell in that column (A31) and it has data in it, I want
it
to go to cell E5 (the start of the 2nd group of columns) and do the same
thing there... only stopping when it hits an empty cell. This would
continue through all 5 sets of data (if necessary) until it finds the
first
empty cell in the Amt column.
Thanks for any help I can get.

Jonco

I'm not sure exactly how it works, but it does. Thank you very much.
Jonco


"Rick Hansen" wrote in message
...
Good Afternoon Jonco,
Here are two marco subs that get the same results. The only difference
in
the two subs is, FindEmpty2() breaks range down into Areas ie each of your
section to search for the empty cells The other FindEmpty1(), treats the
search as on big range.. The results is still the same....

enjoy, Rick, FBKS, AK


Sub FindEmpty2()
Dim Srng As Range, Arng As Range
Dim FoundCell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set Srng = ws.Range("A5:A32,E5:E32,I5:I32,M5:M32,Q5:Q32")

For Each Arng In Srng.Areas

Set FoundCell = Arng.Find(what:="", LookIn:=xlFormulas,
searchorder:=xlByRows)
If Not FoundCell Is Nothing Then
FoundCell.Select '' select empty cell
Exit Sub
End If
Next Arng

End Sub

================================================== ================
Sub FindEmpty1()
Dim Srng As Range
Dim FoundCell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set Srng = ws.Range("A5:A32,E5:E32,I5:I32,M5:M32,Q5:Q32")

Set FoundCell = Srng.Find(what:="", LookIn:=xlFormulas,
searchorder:=xlByRows)
If Not FoundCell Is Nothing Then
FoundCell.Select '' select empty cell
Exit Sub
End If

End Sub



"jonco" wrote in message
. com...
I have a spreadsheet that I'm trying to set up that keeps tack of some
sale
figures (all visible on one screen) in sets of 5 columms with each set
containing 4 cells on each row (Amt, Date, By, Note):
Set 1 (Colums A-D): Amt (Col A), Date (Col B), By (Col C), Note (Col D)
for
each transaction. 27 items in rows 5 - 31
Set 2 (Column E-H): Amt (Col E), Date (Col F), By (Col G), Note (Col H)
for
each transaction. 27 items in rows 5 - 31
Set 3 (Column I-L): Amt (Col I), Date (Col J), By (Col K), Note (Col L)
for
each transaction. 27 items in rows 5 - 31
Set 4 (Column M-P): Amt (Col M), Date (Col N), By (Col O), Note (Col P)
for
each transaction. 27 items in rows 5 - 31
Set 5 (Column Q-T): Amt (Col Q), Date (Col R), By (Col S), Note (Col T)
for
each transaction. 27 items in rows 5 - 31

Now, what I want to do is to have Excel start by going down Colum A (for
the
first group) (starting at Row 5) and find the first empty cell. If it
reaches the last cell in that column (A31) and it has data in it, I want
it
to go to cell E5 (the start of the 2nd group of columns) and do the same
thing there... only stopping when it hits an empty cell. This would
continue through all 5 sets of data (if necessary) until it finds the
first
empty cell in the Amt column.
Thanks for any help I can get.

Jonco