Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: FindNext problem

  1. #1
    Guest

    Default

    I am trying to search two columns of data.
    Since both columns has repeated values in different rows and Find can only find the first value, I need to use FindNext to find the values after. So at the very top I have

    -->Set f = Columns(3).Find(What:=ColrToFind, LookAt:=xlWhole)

    Then inside a While...Wend condition I have

    -->Set f = Columns(3).Cells.FindNext(After:=f)

    but I got an error message "Invalid Procedure Call Or Argument" on line
    -->Set f = Columns(3).Cells.FindNext(After:=f)

    when I run the Macro
    Why???
    Can someone help me?

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 11:11, Anonymous wrote:
    I am trying to search two columns of data.
    Since both columns has repeated values in different rows and Find can only find the first value, I need to use FindNext to find the values after. So at the very top I have

    -->Set f = Columns(3).Find(What:=ColrToFind, LookAt:=xlWhole)

    Then inside a While...Wend condition I have

    -->Set f = Columns(3).Cells.FindNext(After:=f)

    but I got an error message "Invalid Procedure Call Or Argument" on line
    -->Set f = Columns(3).Cells.FindNext(After:=f)

    when I run the Macro
    Why???
    Can someone help me?
    I'm not sure what you're doing but you need to declare "f" as a variable before you can set it. Put something like this at the beginning of your code:

    Dim f As Range

    Hope this helps you out.

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Guest

    Default

    I am trying to create a search userform to search more than two columns of data(but two columns for now) and the data has repeated value. So, the users can enter 2 values to select a specific value or 1 value to select the first value found.
    So what I did was to get the row number of two values entered by user. If those two values are on the same row select the value, if not, it loops and try to find the values on the same row
    So, fRow is the 3rd row and gRow is the 4th row.
    When I run the macro, I got all kinds of error messages in the last outer Else block and especially on the following two lines

    Set f = Columns(3).Cells.FindNext(f)
    fRow = f.Row

    I have been spending too much time debugging the code and my boss is getting angry about my slowlyness. So, I haven't got much time left to finish this project
    The following is the main part of the entire code, I would be greatly appreciated if you could spend some time to look over and correct any logic mistakes or syntex in there.
    Thanks in advance

    The following is the code:

    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
    ElseIf g = "" Then
    f.Activate
    Unload Me
    ElseIf f.Address = g.Offset(0, -1).Address Then
    f.Activate:
    Unload Me
    Else



    Dim fRow As Double
    Dim gRow As Double

    fRow = f.Row
    gRow = g.Row
    'if f's row number = g's row number
    If fRow = gRow Then
    'show data
    fActivate:
    Unload Me
    'elseif f's row number < g's row number
    ElseIf fRow < gRow Then
    MsgBox "fRow < gRow"
    'while f's row number < g's row number

    Do While fRow < gRow

    Set f = Columns(3).Cells.FindNext(f)
    fRow = f.Row

    'endloop

    Loop
    MsgBox fRow & " " & gRow
    'if f's row number = g's row number
    If fRow = gRow Then
    'message found
    f.Activate:
    Unload Me
    Else
    ' 'message notfound
    MsgBox "Message not Found"
    End If
    Else
    MsgBox "frow > grow"
    'while f's row number > g's row number
    Do While fRow > gRow
    'g = g.findnext

    Set g = Columns(4).Cells.FindNext(g)
    gRow = g.Row
    'endloop
    Loop
    'if f's row number = g's row number
    If fRow = gRow Then
    'msgbox found
    g.Activate:
    Unload Me

    Else

    'msgbox not found
    MsgBox "Data not found"
    End If


    End If





    End If

    End If





    End Sub

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is really, really hard to do without having something to test. Is it possible for you to e-mail me a sample of your workbook (with any sensitive data taken out) so I can see what you're doing? I'll try and take a look at it tomorrow if you can. If you can't, I'll try and plug through your code as posted.

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    Guest

    Default

    Anon,

    Was this from one of your posts as well?

    Posted: 2002-03-04 05:35
    --------------------------------------------------------------------------------

    Does anyone know how to create a userform with 3 textboxs to search the excel with repeated values. So user can enter 3 values to search a specific data or enter 1 or 2 values to find the first value found in the excel. The worksheet has the following format

    Weight Color Code
    ------ ----- ----
    30 40 50
    ------ ----- ----
    30 20 50
    ------------------
    30 40 30
    If it is that's good because I have created a 3 criteria search userform for it. I have put this data on Sheet1 in range "B1:D4".

    I have created a userform (UserForm1) with 3 text boxes and 1 commandbutton called "SearchButton". The text beoxes are called: "txtWeight", "txtColour" and "txtCode".

    You type values into these textboxes then hit the button. If the data in all three textboxes is found in the same row, then those three cells are selected. If nothing else, this is an example of a way forward for you. I'm afraid I had to start from scratch because I had to get away from those awful "If...ElseIf..." statements. (That's a semi-serious joke there. ) I haven't done the code for only putting in one variable or two variables. I also haven't put in much error trapping.

    Create the userform1 as described above with the three textboxes and 1 commandbutton. Then insert this code onto the form:


    Private Sub SearchButton_Click()
    MainSearch
    Unload Me
    End Sub


    Then in a standard module, insert this code:


    Option Base 0

    Public Sub Main()
    UserForm1.Show
    End Sub

    Public Sub MainSearch()

    'Declare Variables

    'Search strings
    Dim sWeight As String
    Dim sColour As String
    Dim sCode As String

    'Columns to search
    Dim iWeightCol As Integer
    Dim iColourCol As Integer
    Dim iCodeCol As Integer

    'Declare these as long integers just in case there are more than 32,767 rows being used
    Dim iWeightRows() As Long
    Dim iColourRows() As Long
    Dim iCodeRows() As Long

    'Variable to hold the common row
    Dim iRow As Long

    'Initialise Variables
    With UserForm1
    sWeight = .txtWeight.Value
    sColour = .txtColour.Value
    sCode = .txtCode.Value
    End With

    'Columns to search in
    iWeightCol = 2
    iColourCol = 3
    iCodeCol = 4

    'Create Row Arrays
    CreateRowArray sWeight, iWeightCol, iWeightRows()
    CreateRowArray sColour, iColourCol, iColourRows()
    CreateRowArray sCode, iCodeCol, iCodeRows()

    'Find Common row
    iRow = CommonRow(iWeightRows, iColourRows, iCodeRows)

    'Highlight the Common row
    Range(Cells(iRow, iWeightCol), Cells(iRow, iCodeCol)).Select

    End Sub
    Private Sub CreateRowArray(ByVal sTargetText As String, _
    ByVal iCol As Integer, _
    ByRef iReturnArray() As Long)

    Dim FindRange As Range
    Dim FirstAddress As String
    Dim i As Long

    'Find first Instance of the value
    Set FindRange = Columns(iCol).Find(What:=sTargetText, LookAt:=xlWhole)

    'Make sure a value was found
    If Not FindRange Is Nothing Then
    FirstRange = FindRange.Address
    Do
    'Increment count by 1
    ReDim Preserve iReturnArray(i)
    iReturnArray(i) = FindRange.Row
    Set FindRange = Columns(iCol).FindNext(FindRange)
    i = i + 1
    Loop While Not FindRange Is Nothing And FindRange.Address <> FirstRange

    Else
    MsgBox "Not Found"
    End If

    End Sub
    Private Function CommonRow(ByRef iArrayi() As Long, _
    ByRef iArrayj() As Long, _
    ByRef iArrayk() As Long) As Long
    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    ' CommonRow - This function takes in three arrays as arguments then
    ' compares the values in the arrays to find a common value
    ' in all three
    ' Created - 3/5/02
    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    'Loop variables
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    'Compare Row Arrays
    For i = 0 To UBound(iArrayi())
    For j = 0 To UBound(iArrayj())
    If iArrayi(i) = iArrayj(j) Then
    For k = 0 To UBound(iArrayk())
    If iArrayi(i) = iArrayk(k) Then
    CommonRow = iArrayi(i)
    Exit Function
    End If
    Next
    End If
    Next
    Next

    'If the code gets to here, then no common row was found
    MsgBox "No common row was found for these values"

    End Function



    Run the code from Sub Main and type in your values.

    This code is not designed to handle the 1 variable and 2 variable combos. It has taken me 90 minutes to make this code and it's nearly beddy bo bo's time.

    I'll be out of town for the next two days. Since you haven't put an email address in (and I don't do it myself either) I'm going to email a copy of this example to Barrie Davidson. If you need to see this example then get in touch with Mr. Davidson. Also, if you need help to modify any of the code, I'm sure you will find people willing to help here.

    Why have I spent so much time on this one, I hear you ask?

    1. I know exactly the position you are in and completely empathise with you.
    2. You're boss sounds like the same sort of knobjob that I had to learn VBA for.

    Cheers

  6. #6
    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

    That last post was done by me. Honest. I must have got timed out of the system. "Stay Logged In" hack, my rearend.

    Cheers

  7. #7
    Guest

    Default

    Oh Thank you so much Mr. O'Brian
    You really save my life this time, You are the best of the best

Some videos you may like

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
  •