Searching
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Searching

  1. #1
    Guest

    Default

     
    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

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Guest

    Default

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

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also instead of :


    f=""



    try



    f is nothing





  5. #5
    Guest

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com