Sorting a Loop

N

N1KO

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
 
O

Orion Cochrane

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.
 
C

Chip Pearson

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)
 
N

N1KO

Absolute Legends,

Appreciated both of you

Orion Cochrane said:
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.
Click to expand...
 
Top