Stop Macro

[Guest]

I need to write a macro that will look in one column (unit price) and if the
price is less than $1,000 or blank then stop, otherwise continue.

 

[Guest]

Using column A as example, here's one way

Sub test()
Dim c As Range

For Each c In ActiveSheet.Range("A:A")
If c.Value < 1000 Then
MsgBox "stopped at " & c.Address
Exit Sub
End If
Next c

End Sub

 

[Bill Renaud]

Public Sub CheckPrices()
Dim rngUsedRange As Range
Dim rngUnitPrices As Range
Dim rngCell As Range

Set rngUsedRange = ActiveSheet.UsedRange

With rngUsedRange
'Define rngUnitPrices to be column $A data (without header cell).
Set rngUnitPrices = .Resize(.Rows.Count - 1, 1).Offset(1)
End With

For Each rngCell In rngUnitPrices
If IsEmpty(rngCell) Then GoTo StopCheck
If rngCell.Value < 1000 Then GoTo StopCheck
Next rngCell

MsgBox "All Unit Prices are OK.", _
vbInformation + vbOKOnly, _
"Check Prices"
Exit Sub

StopCheck:
rngCell.Activate
MsgBox "This Unit Price is blank or less than $1,000.", _
vbCritical + vbOKOnly, _
"Check Prices"
End Sub

 

[Guest]

Vergel and Bill:

Thanks for replying, however I have tried both and although both work to
some degree they both seem to find less than 1,000 when indeed there isn't
anything less than 1,000 and also the message says it is blank when indeed
there are no blanks.

Also can we do away with the message box and just stop at the appropriate
cell???

Thanks

 

[Bill Renaud]

I rechecked my macro, and it works fine, as far as I can tell. Is your
"Unit Price" column in column $A, or a different column. I rewrote my
routine slightly to allow for "Unit Price" to be in any column (on row
1). Change the constant blnShowMsgBox at the top of the routine back to
True, if you later decide that you want to show the message boxes again.
Without showing a message box, it is difficult for a user to know if
something happened or not.

Actually, you might be better off just using Data Filter. You will have
to use 2 cases, though, to show only rows where "Unit Price" is <
$1,000, or where "Unit Price" is blank. (Cells where "Unit Price" are
blank will not be displayed when a custom filter is applied to show rows
where "Unit Price" is < $1,000.)

Public Sub CheckPrices()
Const blnShowMsgBox As Boolean = False
Dim wsData As Worksheet
Dim rngUsedRange As Range
Dim rngColumnLabels As Range
Dim rngUnitPrice As Range
Dim rngUnitPriceData As Range
Dim rngCell As Range

Set wsData = ActiveSheet
Set rngUsedRange = wsData.UsedRange

With rngUsedRange
'Assume Column Labels are in row 1.
Set rngColumnLabels = .Resize(1)
Set rngUnitPrice = rngColumnLabels.Find("Unit Price")

'Define rngUnitPrices to be column $A data (without header cell).
Set rngUnitPriceData = rngUnitPrice.Offset(1) _
.Resize(.Rows.Count - 1, 1)
End With

For Each rngCell In rngUnitPriceData
If IsEmpty(rngCell) Then GoTo StopCheck
If rngCell.Value < 1000 Then GoTo StopCheck
Next rngCell

If blnShowMsgBox _
Then
MsgBox "All Unit Prices are OK.", _
vbInformation + vbOKOnly, _
"Check Prices"
End If
Exit Sub

StopCheck:
rngCell.Activate
If blnShowMsgBox _
Then
MsgBox "This Unit Price is blank or less than $1,000.", _
vbCritical + vbOKOnly, _
"Check Prices"
End If
End Sub