My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will locate
the cursor to the next empty cell in the range? What I am looking for is an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

Need more information - what last/empty cell are you looking for? Next empty
one in a row or column? Do we need to consider what the current month or day
is?

While we're figuring that out, here's code with 3 separate 'searches' in it,
pick the one you like or wait for another to come along.

Sub FindFirstEmptyCell()
Dim RP As Integer
Dim CP As Integer
Dim seekRange As Range
Dim anyCell As Range
Dim exitFlag As Boolean

Set seekRange = ActiveSheet.Range("B2:AF13") ' 12 x 31
'this just gets the first empty cell it finds
'pretty much a search by row
For Each anyCell In seekRange
If IsEmpty(anyCell) Then
MsgBox "Empty cell at " & anyCell.Address
anyCell.Select
Exit For
End If
Next
'
'this searches left-to-right then down
'results should be same as above
exitFlag = False
For RP = 2 To 13
For CP = Range("B2").Column To Range("AF13").Column
If IsEmpty(Cells(RP, CP)) Then
MsgBox "Empty cell at " & Cells(RP, CP).Address
Cells(RP, CP).Select
exitFlag = True
Exit For
End If
Next
If exitFlag Then
Exit For
End If
Next
'
'this searches down then left-to-right
exitFlag = False
For CP = Range("B2").Column To Range("AF13").Column
For RP = 2 To 13
If IsEmpty(Cells(RP, CP)) Then
MsgBox "Empty cell at " & Cells(RP, CP).Address
exitFlag = True
Cells(RP, CP).Select
Exit For
End If
Next
If exitFlag Then
Exit For
End If
Next

End Sub


"Stilltrader47" wrote:

My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will locate
the cursor to the next empty cell in the range? What I am looking for is an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

hi
this code is ugly but it works. think i've been up too long.
i assumed that you have a header row in row 1 and data entry starts in row 2.
i assumed that you input start at A2 across to AE2 then down to A3.
if my assumption are not correct then code may be useless.
code goes in a standard module.
Alt+F11.
in the project window(far left), expand the project(file)
if no module then on the vb menu barinsertmodule
paste the code in the code window(far right)
keyboard short cut.
on the menu bartoolsmacromacro
highlight the macro
click the options button.
enter shortcut. ok out.
Sub FindNextEmptyCell()
Dim r, ro As Range
Dim c As Long
c = 1
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(0, 1)
If c 30 Then
Set r = r.Offset(1, -30)
Set ro = r.Offset(0, 1)
c = 1
End If
If Not IsEmpty(r) Then
Set r = ro
c = c + 1
End If
Loop
r.Select
End Sub

regards
FSt1

"Stilltrader47" wrote:

My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will locate
the cursor to the next empty cell in the range? What I am looking for is an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

on second thought, ignore this post.
i thought of something that will make the code not work.
like i said. been up to long. brain not working.
sorry.
FSt1

"FSt1" wrote:

hi
this code is ugly but it works. think i've been up too long.
i assumed that you have a header row in row 1 and data entry starts in row 2.
i assumed that you input start at A2 across to AE2 then down to A3.
if my assumption are not correct then code may be useless.
code goes in a standard module.
Alt+F11.
in the project window(far left), expand the project(file)
if no module then on the vb menu barinsertmodule
paste the code in the code window(far right)
keyboard short cut.
on the menu bartoolsmacromacro
highlight the macro
click the options button.
enter shortcut. ok out.
Sub FindNextEmptyCell()
Dim r, ro As Range
Dim c As Long
c = 1
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(0, 1)
If c 30 Then
Set r = r.Offset(1, -30)
Set ro = r.Offset(0, 1)
c = 1
End If
If Not IsEmpty(r) Then
Set r = ro
c = c + 1
End If
Loop
r.Select
End Sub

regards
FSt1

"Stilltrader47" wrote:

My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will locate
the cursor to the next empty cell in the range? What I am looking for is an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

This may be what you are looking for....

Sub SelectNextEmptyCell()
On Error Resume Next
Range("B2:AF13").SpecialCells(xlCellTypeBlanks)(1) .Select
End Sub

However, if I am right, you could end up picking the wrong cell at the end
of short months (Feb 29th of a non-leap year, April 31st, etc.).

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for is
an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

Try along the lines of;

Sub FindBlank()
Dim rBlank As Range

On Error Resume Next
Set rBlank = _
Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks)(1, 1)
On Error GoTo 0

If Not rBlank Is Nothing Then
MsgBox "Next blank cell in your table is " & _
rBlank.Address
Else
MsgBox "No blanks in range: " & _
Range("A1").CurrentRegion.Address
End If
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"Stilltrader47" wrote in message
...
My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for is
an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

Hello,

I have carried Rick Rothstein's algorithm for considering the length of
various months.
The program finds the next empty cell for each month, not selecting April 31
etc.
The program is rather lengthy because for each month the empty cell has to
be found before applying Mr. Rothstein algorithm for moving the cursor to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in message
...
My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for is
an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

In looking over my code, I'm not completely sure it selects the correct cell
all the time. Here is a different macro that also properly accounts for the
number of days in each month - just set the Jan1st range variable to the
cell corresponding to January 1st (in the Set statement) and the code will
do the rest (I assumed the January 1st cell is B2 in my code)...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
This may be what you are looking for....

Sub SelectNextEmptyCell()
On Error Resume Next
Range("B2:AF13").SpecialCells(xlCellTypeBlanks)(1) .Select
End Sub

However, if I am right, you could end up picking the wrong cell at the end
of short months (Feb 29th of a non-leap year, April 31st, etc.).

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
My worksheet is set-up for 12 month price tracking. The entry range is
12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for is
an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

I just posted a new macro (in response to my previous posting) that you may
find interesting... it properly handles the end of month transitions and it
is not what I would call "lengthy" code.

--
Rick (MVP - Excel)



"helene and gabor" wrote in message
...
Hello,

I have carried Rick Rothstein's algorithm for considering the length of
various months.
The program finds the next empty cell for each month, not selecting April
31 etc.
The program is rather lengthy because for each month the empty cell has to
be found before applying Mr. Rothstein algorithm for moving the cursor to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in message
...
My worksheet is set-up for 12 month price tracking. The entry range is
12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for is
an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

I have used JLatham's program to consider the number of days each month.

best regards,

Gabor Sebo
"Stilltrader47" wrote in message
..
-----------------------------------------------------------------------------------------------------------
'JLatham's program expanded 4/17/2010
'TABLE IN B2: AF13

Sub FindFirstEmptyCell()
Dim RP As Integer
Dim CP As Integer
Dim seekRange As Range
Dim anyCell As Range
Dim exitFlag As Boolean

Set seekRange = ActiveSheet.Range("B2:AF13") ' 12 x 31
'this just gets the first empty cell it finds
'pretty much a search by row
For Each anyCell In seekRange
If IsEmpty(anyCell) Then
'MsgBox "Empty cell at " & anyCell.Address
anyCell.Select
m = anyCell.Address


If m = "$AD$3" Or m = "$AE$3" Or m = "$AF$3" Or m = "$AF$5" Or m =
"$AF$7" Or m = "$AF$10" Or m = "$AF$12" Then
' MsgBox "NONO" & m
' MsgBox " Caught"
GoTo rest
End If

Exit For
End If
rest:
Next
'
'this searches left-to-right then down
'results should be same as above

End Sub





"Stilltrader47" wrote in message
...
My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for is
an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

I have used JLatham's program to consider the number of days each month.

best regards,

Gabor Sebo
"Stilltrader47" wrote in message
..
-----------------------------------------------------------------------------------------------------------
'JLatham's program expanded 4/17/2010
'TABLE IN B2: AF13

Sub FindFirstEmptyCell()
Dim RP As Integer
Dim CP As Integer
Dim seekRange As Range
Dim anyCell As Range
Dim exitFlag As Boolean

Set seekRange = ActiveSheet.Range("B2:AF13") ' 12 x 31
'this just gets the first empty cell it finds
'pretty much a search by row
For Each anyCell In seekRange
If IsEmpty(anyCell) Then
'MsgBox "Empty cell at " & anyCell.Address
anyCell.Select
m = anyCell.Address


If m = "$AD$3" Or m = "$AE$3" Or m = "$AF$3" Or m = "$AF$5" Or m =
"$AF$7" Or m = "$AF$10" Or m = "$AF$12" Then
' MsgBox "NONO" & m
' MsgBox " Caught"
GoTo rest
End If

Exit For
End If
rest:
Next
'
'this searches left-to-right then down
'results should be same as above

End Sub





"Stilltrader47" wrote in message
...

J, Good question. And thanks for your continued review of my issue. The
current date is relevant, here's why. I will not be populating every cell in
the range with a price value. Days will be skipped, meaning cells in the
range will be left empty. Below is a brief example to help illustrate.

April
(Col) R S T U V
W
13 4/17/10 4/18/10 4/19/10 4/20/10 4/21/10 4/22/10
14 5.00 4.75


See that for April, the 19th, 20th and 21st do not have a price value
entered. So when the macro is run on 4/22, it should really be looking for
the next empty cell in the range equla to or approximate to the current date.
Like Row 13 above, dates will be pre-populated for all 12 months.

I hope this better explains what I am looking for. Thanks Tom



"JLatham" wrote:

Need more information - what last/empty cell are you looking for? Next empty
one in a row or column? Do we need to consider what the current month or day
is?

While we're figuring that out, here's code with 3 separate 'searches' in it,
pick the one you like or wait for another to come along.

Sub FindFirstEmptyCell()
Dim RP As Integer
Dim CP As Integer
Dim seekRange As Range
Dim anyCell As Range
Dim exitFlag As Boolean

Set seekRange = ActiveSheet.Range("B2:AF13") ' 12 x 31
'this just gets the first empty cell it finds
'pretty much a search by row
For Each anyCell In seekRange
If IsEmpty(anyCell) Then
MsgBox "Empty cell at " & anyCell.Address
anyCell.Select
Exit For
End If
Next
'
'this searches left-to-right then down
'results should be same as above
exitFlag = False
For RP = 2 To 13
For CP = Range("B2").Column To Range("AF13").Column
If IsEmpty(Cells(RP, CP)) Then
MsgBox "Empty cell at " & Cells(RP, CP).Address
Cells(RP, CP).Select
exitFlag = True
Exit For
End If
Next
If exitFlag Then
Exit For
End If
Next
'
'this searches down then left-to-right
exitFlag = False
For CP = Range("B2").Column To Range("AF13").Column
For RP = 2 To 13
If IsEmpty(Cells(RP, CP)) Then
MsgBox "Empty cell at " & Cells(RP, CP).Address
exitFlag = True
Cells(RP, CP).Select
Exit For
End If
Next
If exitFlag Then
Exit For
End If
Next

End Sub


"Stilltrader47" wrote:

My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will locate
the cursor to the next empty cell in the range? What I am looking for is an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

Rick, where did you post the macro? Thanks - Tom

"Rick Rothstein" wrote:

I just posted a new macro (in response to my previous posting) that you may
find interesting... it properly handles the end of month transitions and it
is not what I would call "lengthy" code.

--
Rick (MVP - Excel)



"helene and gabor" wrote in message
...
Hello,

I have carried Rick Rothstein's algorithm for considering the length of
various months.
The program finds the next empty cell for each month, not selecting April
31 etc.
The program is rather lengthy because for each month the empty cell has to
be found before applying Mr. Rothstein algorithm for moving the cursor to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in message
...
My worksheet is set-up for 12 month price tracking. The entry range is
12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for is
an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.


.

It was in the response to my own posting. This is the code I posted there...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

Don't forget to set the Jan1st Range variable to the cell corresponding to
January 1st.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
Rick, where did you post the macro? Thanks - Tom

"Rick Rothstein" wrote:

I just posted a new macro (in response to my previous posting) that you
may
find interesting... it properly handles the end of month transitions and
it
is not what I would call "lengthy" code.

--
Rick (MVP - Excel)



"helene and gabor" wrote in message
...
Hello,

I have carried Rick Rothstein's algorithm for considering the length
of
various months.
The program finds the next empty cell for each month, not selecting
April
31 etc.
The program is rather lengthy because for each month the empty cell has
to
be found before applying Mr. Rothstein algorithm for moving the cursor
to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in
message
...
My worksheet is set-up for 12 month price tracking. The entry range
is
12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for
is
an
efficient "find" command. I would prefer to execute by ctl-(letter)
if
possible.

Thanks for helping.


.

Thanks Rick I am going now to run it

"Rick Rothstein" wrote:

It was in the response to my own posting. This is the code I posted there...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

Don't forget to set the Jan1st Range variable to the cell corresponding to
January 1st.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
Rick, where did you post the macro? Thanks - Tom

"Rick Rothstein" wrote:

I just posted a new macro (in response to my previous posting) that you
may
find interesting... it properly handles the end of month transitions and
it
is not what I would call "lengthy" code.

--
Rick (MVP - Excel)



"helene and gabor" wrote in message
...
Hello,

I have carried Rick Rothstein's algorithm for considering the length
of
various months.
The program finds the next empty cell for each month, not selecting
April
31 etc.
The program is rather lengthy because for each month the empty cell has
to
be found before applying Mr. Rothstein algorithm for moving the cursor
to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in
message
...
My worksheet is set-up for 12 month price tracking. The entry range
is
12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for
is
an
efficient "find" command. I would prefer to execute by ctl-(letter)
if
possible.

Thanks for helping.


.

.

Rick - I could not get it to run. I attached ctl-d to launch it. It should
have went to Jan1st, right? If your interested I'll send you the file, would
appreciate. Tom

"Rick Rothstein" wrote:

It was in the response to my own posting. This is the code I posted there...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

Don't forget to set the Jan1st Range variable to the cell corresponding to
January 1st.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
Rick, where did you post the macro? Thanks - Tom

"Rick Rothstein" wrote:

I just posted a new macro (in response to my previous posting) that you
may
find interesting... it properly handles the end of month transitions and
it
is not what I would call "lengthy" code.

--
Rick (MVP - Excel)



"helene and gabor" wrote in message
...
Hello,

I have carried Rick Rothstein's algorithm for considering the length
of
various months.
The program finds the next empty cell for each month, not selecting
April
31 etc.
The program is rather lengthy because for each month the empty cell has
to
be found before applying Mr. Rothstein algorithm for moving the cursor
to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in
message
...
My worksheet is set-up for 12 month price tracking. The entry range
is
12
rows (months) by 31 columns (days). How can I write a macro that will
locate
the cursor to the next empty cell in the range? What I am looking for
is
an
efficient "find" command. I would prefer to execute by ctl-(letter)
if
possible.

Thanks for helping.


.

.

Yes, you can send the file to me.... just remove the NO.SPAM stuff from my
email address.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
Rick - I could not get it to run. I attached ctl-d to launch it. It
should
have went to Jan1st, right? If your interested I'll send you the file,
would
appreciate. Tom

"Rick Rothstein" wrote:

It was in the response to my own posting. This is the code I posted
there...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As
Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

Don't forget to set the Jan1st Range variable to the cell corresponding
to
January 1st.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in
message
...
Rick, where did you post the macro? Thanks - Tom

"Rick Rothstein" wrote:

I just posted a new macro (in response to my previous posting) that
you
may
find interesting... it properly handles the end of month transitions
and
it
is not what I would call "lengthy" code.

--
Rick (MVP - Excel)



"helene and gabor" wrote in message
...
Hello,

I have carried Rick Rothstein's algorithm for considering the
length
of
various months.
The program finds the next empty cell for each month, not selecting
April
31 etc.
The program is rather lengthy because for each month the empty cell
has
to
be found before applying Mr. Rothstein algorithm for moving the
cursor
to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester
has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in
message
...
My worksheet is set-up for 12 month price tracking. The entry
range
is
12
rows (months) by 31 columns (days). How can I write a macro that
will
locate
the cursor to the next empty cell in the range? What I am looking
for
is
an
efficient "find" command. I would prefer to execute by
ctl-(letter)
if
possible.

Thanks for helping.


.

.

Rick - I sent the file and a results script to and the
email was returned by MAILER-DAEMON (not found). I have the file ready to
provide to you for review. Please advise here, or
Thanks - Tom

"Rick Rothstein" wrote:

Yes, you can send the file to me.... just remove the NO.SPAM stuff from my
email address.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
Rick - I could not get it to run. I attached ctl-d to launch it. It
should
have went to Jan1st, right? If your interested I'll send you the file,
would
appreciate. Tom

"Rick Rothstein" wrote:

It was in the response to my own posting. This is the code I posted
there...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As
Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

Don't forget to set the Jan1st Range variable to the cell corresponding
to
January 1st.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in
message
...
Rick, where did you post the macro? Thanks - Tom

"Rick Rothstein" wrote:

I just posted a new macro (in response to my previous posting) that
you
may
find interesting... it properly handles the end of month transitions
and
it
is not what I would call "lengthy" code.

--
Rick (MVP - Excel)



"helene and gabor" wrote in message
...
Hello,

I have carried Rick Rothstein's algorithm for considering the
length
of
various months.
The program finds the next empty cell for each month, not selecting
April
31 etc.
The program is rather lengthy because for each month the empty cell
has
to
be found before applying Mr. Rothstein algorithm for moving the
cursor
to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester
has
expected.

Best Regards,

Gabor Sebo

"Stilltrader47" wrote in
message
...
My worksheet is set-up for 12 month price tracking. The entry
range
is
12
rows (months) by 31 columns (days). How can I write a macro that
will
locate
the cursor to the next empty cell in the range? What I am looking
for
is
an
efficient "find" command. I would prefer to execute by
ctl-(letter)
if
possible.

Thanks for helping.


.

.

.

Thank you SO MUCH for posting my "unmunged" email address in this open
newsgroup where all the spambots can find it and add it to their spam lists.
I am really SO HAPPY you did that for me. Exactly why do you think I post my
email address with the NO.SPAM stuff in there... for my health?

--
Rick (MVP - Excel)

Rick, My apology. It was not my intent to expose your id for malicious
purposes. I was too focused on my macro issue, and need to resolve. I
should have been more observant.

"Rick Rothstein" wrote:

Thank you SO MUCH for posting my "unmunged" email address in this open
newsgroup where all the spambots can find it and add it to their spam lists.
I am really SO HAPPY you did that for me. Exactly why do you think I post my
email address with the NO.SPAM stuff in there... for my health?

--
Rick (MVP - Excel)

.

Phew! In looking more closely at your posting, it looks like I lucked out as
you didn't post my correct email address... there is supposed to be a dot
between the "rick" and the "news" part. That is probably why it your email
didn't get delivered to me. Try again.

--
Rick (MVP - Excel)



"Stilltrader47" wrote in message
...
Rick, My apology. It was not my intent to expose your id for malicious
purposes. I was too focused on my macro issue, and need to resolve. I
should have been more observant.

"Rick Rothstein" wrote:

Thank you SO MUCH for posting my "unmunged" email address in this open
newsgroup where all the spambots can find it and add it to their spam
lists.
I am really SO HAPPY you did that for me. Exactly why do you think I post
my
email address with the NO.SPAM stuff in there... for my health?

--
Rick (MVP - Excel)

.