Search and find

[Rick K]

Need help on creating a routine that loops down through a
spreadsheet's rows that contain entries and compares the
entry in column 1 (State abrevation) with column 2 entry
(shipment method). Specificly I want to return a msgbox to
the user when the state = "HI" or "AK" and the ship method
is "ground", and then quits the vba form so the user can
correct the situation.
Thnaks

 

[Dave Peterson]

Something like this:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
Select Case LCase(.Value)
Case "ak", "hi"
.Select
MsgBox "error on row: " & .Row
Exit Sub
Case Else
'do nothing
End Select
End With
Next myCell

End Sub

(not sure how you're userform works, though.)

Couldn't they just take the interstate to hi?

 

[Rick K]

Thanks, I'll ive this a try and advise.
Rick

-----Original Message-----
Something like this:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells (.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
Select Case LCase(.Value)
Case "ak", "hi"
.Select
MsgBox "error on row: " & .Row
Exit Sub
Case Else
'do nothing
End Select
End With
Next myCell

End Sub

(not sure how you're userform works, though.)

Couldn't they just take the interstate to hi?





--

Dave Peterson
(e-mail address removed)
.

 

[RickK]

Well, I tried your method, but sorry to say it didn't
work. But the upside is that I figured out a way to do it.
This method works very well. This code is running in a
module in my vba

'This routine searches in column "H" for the state
abreviation (s.value) and
'also returns the contents of column "M" (S.Offset) which
contain the shipment mehod
'compares the values and erors out with a message when
certain conditions are met.
'Starts at row 2 (row 1 has is a header row)

Sub CheckShip()
Dim S As Range
Dim T

With Worksheets("Sheet1")
For Each S In Range("H2:H" & Cells(Rows.Count, "H").End
(xlUp).Row)
T = S.Offset(0, 5).Text 'get shipment method
If S.Value = "" Then 'bail out when reaching the row
below the last entry
GoTo 30
ElseIf S.Value = "AK" Or S.Value = "HI" And T
= "Ground" Then
MsgBox ("SHIPMENT METHOD ERROR: Alaska 'AK' or
Hawaii 'HI'" & vbLf & _
"destination showing as a GROUND shipment, please
correct."), vbCritical, "Shipment method Error"
Exit Sub
End If
Next

End With
30 Call RunAll
End Sub