I have a Sub that dumps information into a worksheet. When a certain amount a
data is dumped, the routine makes a call to another sub to find a clear cell
and then it continues. My range is only from $L$19 to $L$45 and I would like
it if I could use the "new cell" routine to force the termination of the data
dump routine when the range has been exceeded. Possible?

Here's the "new cell" code:

Sub newCell()
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name < "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
If (whiteSpace.test(ActiveCell.value)) Then
Exit For
ElseIf curCell 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

Perhaps something like this:

Sub DumpData()

' existing code
if not newCell then exit sub
' existing code

End sub


Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name < "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
End If
Next
NewCell = True
End Function

--
Regards,
Tom Ogilvy


"IT_roofer" wrote:

I have a Sub that dumps information into a worksheet. When a certain amount a
data is dumped, the routine makes a call to another sub to find a clear cell
and then it continues. My range is only from $L$19 to $L$45 and I would like
it if I could use the "new cell" routine to force the termination of the data
dump routine when the range has been exceeded. Possible?

Here's the "new cell" code:

Sub newCell()
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name < "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
If (whiteSpace.test(ActiveCell.value)) Then
Exit For
ElseIf curCell 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

IT, make newCell a Function so it can return a value to the calling routine.
James

Function newCell() as Boolean
newCell=True

<snip

ElseIf curCell 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell=False
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Function

"Tom Ogilvy" wrote:

Perhaps something like this:

Sub DumpData()

' existing code
if not newCell then exit sub
' existing code

End sub


Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name < "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
End If
Next
NewCell = True
End Function

--
Regards,
Tom Ogilvy



Thank you for the suggestion. I will try it. One question: doesn't the
"NewCell = True" statement at the end override the "NewCell = False"
statement in the "ElseIf curCell = 45" - or is that just to reset "NewCell"
when the For loop is over?

Guess I didn't finish putting in the exit function statements:

Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name < "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
Exit Function
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
Exit Function
End If
Next
NewCell = True
End Function

With the Exit functions added, it should never reach that statement.

--
Regards,
Tom Ogilvy


"IT_roofer" wrote:

"Tom Ogilvy" wrote:

Perhaps something like this:

Sub DumpData()

' existing code
if not newCell then exit sub
' existing code

End sub


Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name < "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
End If
Next
NewCell = True
End Function

--
Regards,
Tom Ogilvy



Thank you for the suggestion. I will try it. One question: doesn't the
"NewCell = True" statement at the end override the "NewCell = False"
statement in the "ElseIf curCell = 45" - or is that just to reset "NewCell"
when the For loop is over?

"Zone" wrote:

IT, make newCell a Function so it can return a value to the calling routine.
James

Function newCell() as Boolean
newCell=True

<snip

ElseIf curCell 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell=False
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Function




Thanks Guys for the suggestions.

I noticed Tom put "newCell = True" after the For loop and Zone/James put it
ahead of the For loop. Do I have to declare newcell = True/False outside of
the For loop at all?

Tom: I added in an "Exit For" (in code below) to get it to stop cycling
through the cells to cell L45. There's a locked cell @L46 and it generated an
error about accessing locked cells. Otherwise your code worked perfect.
Thanks!

Zone/James: I still don't quite get the difference between a function and a
sub, but i guess that's part of learning! Maybe you could shed some light?

(code)
If (whiteSpace.test(ActiveCell.value)) Then
newCell = True
Exit For '<======[ THIS ]

"Tom Ogilvy" wrote:

Guess I didn't finish putting in the exit function statements:

Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name < "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
Exit Function
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
Exit Function
End If
Next
NewCell = True
End Function

With the Exit functions added, it should never reach that statement.

--
Regards,
Tom Ogilvy


Ah, ok. When I got the error (see reply to "Zone") I noticed there wasn't an
exit statement so I put it in.

Thanks again, Tom! You rock. :)

IT,
I like to set the function's return value at the top if possible so that I'm
sure what state the function starts out with. But you could do it either my
way or Tom's way. Either way, you do need to set the function's value to
something in any case. In my test, if you never set the function's return
value, it just returns False. The difference between a sub and a function
is really very simple. A function can return a value. A sub cannot. James
"IT_roofer" wrote in message
...
"Zone" wrote:

IT, make newCell a Function so it can return a value to the calling
routine.
James

Function newCell() as Boolean
newCell=True

<snip

ElseIf curCell 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell=False
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Function




Thanks Guys for the suggestions.

I noticed Tom put "newCell = True" after the For loop and Zone/James put
it
ahead of the For loop. Do I have to declare newcell = True/False outside
of
the For loop at all?

Tom: I added in an "Exit For" (in code below) to get it to stop cycling
through the cells to cell L45. There's a locked cell @L46 and it generated
an
error about accessing locked cells. Otherwise your code worked perfect.
Thanks!

Zone/James: I still don't quite get the difference between a function and
a
sub, but i guess that's part of learning! Maybe you could shed some light?

(code)
If (whiteSpace.test(ActiveCell.value)) Then
newCell = True
Exit For '<======[ THIS ]