Range help

[Rick S.]

If the Dim "sUserPart" has no match in column A, how do I capture that? I
would like to prompt the user with a MsgBox.
Everything I have come up with itterates thru all the cells.

'======
With Sheets("Part Number")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A1:A" & Sh1LastRow)
End With
For Each Sh1Cell In Sh1Range
If Sh1Cell.Value = sUserPart Then
MsgBox "do some stuff here"
Else
MsgBox "Not found!" 'loops thru all cells in range?
End If
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Joel]

with your code just add another variable found. Also you should add an exit
for to speed up the code.

Found = false
With Sheets("Part Number")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A1:A" & Sh1LastRow)
End With
For Each Sh1Cell In Sh1Range
If Sh1Cell.Value = sUserPart Then
MsgBox "do some stuff here"
found = true
exit for
Else
MsgBox "Not found!" 'loops thru all cells in range?
End If


Another way of doing the same thing
set c = columns("A:A").find(what:=sUserPart,lookin:=xlvalues,lookat:xlwhole)
if not c is nothing then
MsgBox "do some stuff here"
Else
MsgBox "Not found!" 'loops thru all cells in range?
End If

 

[Rick S.]

"found = true"
GENIOUS!

It is uncanny how these things can be resolved with such simplicity,
unfotunately for me my complex brain can't do simple. LOL
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Dave Peterson]

You could use =match() in a worksheet cell to look for a match.

In code:

Dim Res as Variant
dim RngToCheck as range
dim myVal as String ' or variant or long or ...

with worksheets("sheet9999")
set rngtocheck = .range("A:a")
end with

myval = "somepartnumber"

res = application.match(myval, rngtocheck, 0)

if iserror(res) then
'no match
else
'found a match
end if