FindNext problem

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?

[Barrie Davidson]

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,

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

[Barrie Davidson]

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,

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

[Mark O'Brien]

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

Cheers

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