Searching

[Guest]

Hello people
I am trying to do a search function however, a line of my code
Loop Until f.Offset(0, 0) = g.Offset(0, -1) Or f = ""
gave me an error message(no set variable or with block variable)
I couldn't figure out how to solve this problem
please help
Thank you
The following is part of the code

If f.Address = g.Offset(0, -1).Address Then
f.Activate:
Unload Me
Else




Do
Set f = Columns(3).FindNext(After:=f)
Loop Until f.Offset(0, 0) = g.Offset(0, -1) Or f = ""


If f.Address = g.Offset(0, -1).Address Then
MsgBox f.Address:
MsgBox g.Address:
f.Activate:
Unload Me


Else
MsgBox "No Data"
End If
End If

 

[Damon Ostrander]

I think the problem here is that

Loop Until f.Offset(0, 0) = g.Offset(0, -1) Or f = ""

should be

Loop Until f.Offset(0, 0).Valu = g.Offset(0, -1).Value Or f = ""

because otherwise it is testing two Range objects for equality, rather than the contents of the range objects.

 

[Guest]

Ahhh, it still gives me the same error message
Thank you Damon for trying to help

 

[Mark O'Brien]

Also instead of :

<pre>
f=""

</pre>

try

<pre>

f is nothing

</pre>

 

[Guest]

I have already tried f is nothing. It still gives me the same error message.
The following is my entire userform code in search button, could anyone find any mistakes in here? I started learning VBA only a couple of days ago, so there are probably a lot of syntex errors
Appreciated for helping folks


Private Sub SearchButton_Click()
MillToFind = tbMillToFind.Text
GrdeToFind = tbGrdeToFind.Text
ColrToFind = tbColrToFind.Text
BswtToFind = tbBswtToFind.Text
LongGradeDescriptionToFind = tbLongGradeDescription.Text

Set e = Columns(2).Find(What:=GrdeToFind, LookAt:=xlWhole)
Set f = Columns(3).Find(What:=ColrToFind, LookAt:=xlWhole)
Set g = Columns(4).Find(What:=BswtToFind, LookAt:=xlWhole)


If f Is Nothing Then
MsgBox ColrToFind & "Colour Code was not found.", vbInformation, "Result"

With tbColrToFind
.SelStart = 0
.SelLength = 100
.SetFocus
End With
Exit Sub
ElseIf f = "" Then
If g Is Nothing Then
MsgBox BswtToFind & "Basis Weight was not found.", vbInformation, "Result"
With tbBswtToFind
.SelStart = 0
.SelLength = 100
.SetFocus
End With
Exit Sub
ElseIf g = "" Then

Else
g.Activate
Unload Me
End If



Else
If g Is Nothing Then

MsgBox BswtToFind & "Basis Weight was not found.", vbInformation, "Result"

With tbBswtToFind
.SelStart = 0
.SelLength = 100
.SetFocus
End With
Exit Sub
Else
If g = "" Then
f.Activate
Unload Me
Else

If f.Address = g.Offset(0, -1).Address Then
f.Activate:
Unload Me
Else



firstAddress = f.Address
Do
Set f = Columns(3).FindNext(After:=f)
Loop Until f.Offset(0, 0).Value = g.Offset(0, -1).Value Or f Is Nothing



If f.Address = g.Offset(0, -1).Address Then
MsgBox f.Address:
MsgBox g.Address:
f.Activate:
Unload Me


Else
MsgBox "No Data"
End If
End If



End If
End If
End If





End Sub