Sorting a Loop

  • Thread starter Thread starter N1KO
  • Start date Start date
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
 
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.
 
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)
 
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...
 
Back
Top