Searching

G

Guest

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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
Ahhh, it still gives me the same error message
Thank you Damon for trying to help
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top