Validate Entry

[Guest]

After a User has entered data in a spreadsheet, I want to verify that the
data is valid. I assign a variable to the data I want to validate, go to the
table where the valid data resides and try to do a search but I get a message
"Object Variable or with Block Variable Not Set". Here is basically the code
I am using:

Sub Macro2()
Dim acct As Object
Dim searchfound As String
Cells(1, 1).Select
acct = "x"
Cells.Find(What:=acct, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

searchfound = ActiveCell.Value
If searchfound = acct Then
MsgBox ("Found it")
Else
MsgBox ("Did not find it")
End If

End Sub

 

[Guest]

Mike

A slightly different approach:-

Sub Macro2()
Dim acct As String
Dim searchfound As String
Dim myRange As Range
Set myRange = Range("A1:A100") '< Alter to suit
acct = "x"

For Each c In myRange
If c.Value = acct Then flag = 1
Next
If flag = 1 Then
MsgBox ("Found it")
Else
MsgBox ("Did not find it")
End If
End Sub

Mike

 

[Guest]

fThis works pretty well but two questions. The area I am to search is pretty
long so how do i exit the for loop if flag=1.
And how do I pass the variable acct to the sub instead of setting its value
within the sub?

 

[Guest]

Mike Try,

Sub Macro2()
acct = InputBox("What are we looking for")
Dim searchfound As String
Dim myRange As Range
Set myRange = Range("A1:A100")
For Each c In myRange
If c.Value = acct Then flag = 1
If flag = 1 Then GoTo 100
Next
100
If flag = 1 Then
MsgBox ("Found it")
Else
MsgBox ("Did not find it")
End If
End Sub

 

[Dave Peterson]

How about something like:

Dim FoundCell as range
'...do some stuff
'try to do your find
set foundcell = cells.find(...) 'no .activate here!!!

if foundcell is nothing then
'not found
else
'found it
msgbox foundcell.address 'for example
end if