Hi,

I'm trying to loop the code below until row 38, I basically want it to check
B8 with V8, B9 with V9 etc but i cannot for the life of me remember how to
loop this. I want an error box to appear when it finds an error and when this
happens i want the loop to stop.

Any help would be appreciated

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Price Change")

If Range("B8").Value = "" And Range("V8").Value < "" Then
MsgBox "You cannot enter a date when there is not a code to change
price"
Cancel = True
End If
End With


End Sub

Here's what I would try (untested):

Private Sub Workbook_BeforeClose(Cancel as Boolean)
Sheets("Price Change").Activate
Range("B8").Select
Do
If ActiveCell.Value = "" and ActiveCell.Offset(0,20).Value < "" Then
MsgBox <Message
Cancel = True
End If
ActiveCell.Offset(1,0).Select
Loop Until ActiveCell.Row=38
End Sub

I do not know if it will not execute at row 38. If not, change to 39.
--
I am running on Excel 2003, unless otherwise stated.


"N1KO" wrote:

Hi,

I'm trying to loop the code below until row 38, I basically want it to check
B8 with V8, B9 with V9 etc but i cannot for the life of me remember how to
loop this. I want an error box to appear when it finds an error and when this
happens i want the loop to stop.

Any help would be appreciated

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Price Change")

If Range("B8").Value = "" And Range("V8").Value < "" Then
MsgBox "You cannot enter a date when there is not a code to change
price"
Cancel = True
End If
End With


End Sub

Try something like the following:

Dim RowNdx As Long
Dim StartRow As Long
StartRow = ActiveCell.Row '<<< Initialize row as desired
With Worksheets("Price Change")
For RowNdx = StartRow To 38
If (.Cells(RowNdx, "B").Value = vbNullString) And _
(.Cells(RowNdx, "V").Value < vbNullString) Then
MsgBox "Error On Row: " & CStr(RowNdx)
Exit For
End If
Next RowNdx
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 31 Oct 2008 08:44:11 -0700, N1KO
wrote:

Hi,

I'm trying to loop the code below until row 38, I basically want it to check
B8 with V8, B9 with V9 etc but i cannot for the life of me remember how to
loop this. I want an error box to appear when it finds an error and when this
happens i want the loop to stop.

Any help would be appreciated

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Price Change")

If Range("B8").Value = "" And Range("V8").Value < "" Then
MsgBox "You cannot enter a date when there is not a code to change
price"
Cancel = True
End If
End With


End Sub

Absolute Legends,

Appreciated both of you

"Orion Cochrane" wrote:

Here's what I would try (untested):

Private Sub Workbook_BeforeClose(Cancel as Boolean)
Sheets("Price Change").Activate
Range("B8").Select
Do
If ActiveCell.Value = "" and ActiveCell.Offset(0,20).Value < "" Then
MsgBox <Message
Cancel = True
End If
ActiveCell.Offset(1,0).Select
Loop Until ActiveCell.Row=38
End Sub

I do not know if it will not execute at row 38. If not, change to 39.
--
I am running on Excel 2003, unless otherwise stated.


"N1KO" wrote:

Hi,

I'm trying to loop the code below until row 38, I basically want it to check
B8 with V8, B9 with V9 etc but i cannot for the life of me remember how to
loop this. I want an error box to appear when it finds an error and when this
happens i want the loop to stop.

Any help would be appreciated

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Price Change")

If Range("B8").Value = "" And Range("V8").Value < "" Then
MsgBox "You cannot enter a date when there is not a code to change
price"
Cancel = True
End If
End With


End Sub