excel vba search btn

[DJ2018]

Hi guys im trying to create a searchable user form but have come unstuck.
When I enter the search details in txtAmount - my search box; hit the find btn, I get a runtime error 9 Subscript out of range.
This is my code any help would be cool.

Private Sub cmdFind_Click()
Dim totRows As Long, i As Long

totRows = Worksheets("Sheet2").Range("C1").CurrentRegion.Rows.Count

If txtAmount = "" Then
MsgBox "Enter Search Amount"
End If


For i = 2 To totRows
If Trim(Sheet2.Cells(i, 3)) <> Trim(txtAmount.Text) And i = totRows Then
MsgBox "Not Found"
End If
If Trim(Sheet2.Cells(i, 3)) = Trim(txtAmount.Text) Then
txtcmr.Text = Sheet2.Cells(i, 2)
txtAccNo.Text = Sheet2.Cells(i, 9)

Exit For
End If
Next i

End Sub

 

[Andrzejek]

First, please format your code like this:

Private Sub cmdFind_Click()
Dim totRows As Long, i As Long

totRows = Worksheets("Sheet2").Range("C1").CurrentRegion.Rows.Count

If txtAmount = "" Then
   MsgBox "Enter Search Amount"
End If

For i = 2 To totRows
    If Trim(Sheet2.Cells(i, 3)) <> Trim(txtAmount.Text) And i = totRows Then
         MsgBox "Not Found"
    End If
    If Trim(Sheet2.Cells(i, 3)) = Trim(txtAmount.Text) Then
        txtcmr.Text = Sheet2.Cells(i, 2)
        txtAccNo.Text = Sheet2.Cells(i, 9)
        Exit For
    End If
Next i

End Sub

Second, which line of code do you get the error on?

FYI - even if you do not enter any amount, you still do the search.
If you do enter amount, you only find the very first occurrence of this amount, and you quit looking, but you may have another one (amount) down the rows.



---- Andy

There is a great need for a sarcasm font.

 

[combo]

I filled whole column C, copied your code to the userform and run it. No errors reported.

Is there a part of code in procedure that you deleted?
Is the code and data in the same workbook?
Do you add worksheet programmatically?
Anything other specific?

combo

 

[DJ2018]

Sorry for the code it was formatted when I copied it on here.

the runtime error happens after I added the search criteria into the txtAmount on the userform and click the cmdfind when I debug it stops here

totRows = Worksheets("Sheet2").Range("C1").CurrentRegion.Rows.Count with the msg runtime error 9 Subscript out of range.

im aiming for the search to loop column C and show the first search match, then if I click cmdfind the code moves to the next range.

many thanks

 

[DJ2018]

the form search and data are all in the same workbook and that is my full code for the search btn

Thanks

 

[SkipVought]

totRows = Worksheets("Sheet2").Range("C1").CurrentRegion.Rows.Count with the msg runtime error 9 Subscript out of range.

Then you do not have a sheet with a TAB named Sheet2. Maybe a trailing SPACE?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

then if I click cmdfind the code moves to the next range.

Not with the code you have now.
When you find the first Amount, you are done and you exit the For loop with [tt]Exit For[/tt] and your Click event is done.
If you click cmdFind again, you start from the top and you get the same outcome: find first Amount (again) and that's it.


---- Andy

There is a great need for a sarcasm font.

 

[DJ2018]

My Sheet2 the tab is actually named Account, all sorted now. Many thanks to all

 

[DJ2018]

Andrejek im out of my depth now are you able to assist with the code to move to the next record?

 

[Andrzejek]

Sure, but that all depends on what you want to happen.
If you have an Amount of 100 in 5 different rows in your column C, and you hit Find button, what do you want to happen?

You may say: "I want to highlight in Yellow all amounts of 100 in column C", or you may want: "Display in a message box which row(s) have a 100 Amount", or "I want to have a list of Account Numbers with the amount of 100", or, whatever....


---- Andy

There is a great need for a sarcasm font.

 

[DJ2018]

just to move to the next occurrence ive tidied the code a little.

Private Sub cmdFind_Click()
Dim totRows As Long, i As Long

totRows = Worksheets("SUSPENSE ACCOUNT").Range("C1").CurrentRegion.Rows.Count

If txtAmount = "" Then

​

MsgBox "Enter Search Amount"
End If

For i = 2 To totRows

​

If Trim(Sheet2.Cells(i, 3)) <> Trim(txtAmount.Text) And i = totRows Then

​

MsgBox "Not Found"

End If

If Trim(Sheet2.Cells(i, 3)) = Trim(txtAmount.Text) Then

txtcmr.Text = Sheet2.Cells(i, 6)

txtAccNo.Text = Sheet2.Cells(i, 12)

Exit For

End If
Next i

End Sub​

 

[Andrzejek]

To format the code use:

[ignore]

If Something = XYZ Then
    Do something
End if

[/ignore]

Which will give you:

If Something = XYZ Then
    Do something
End if

Or - highlight your code and click the icon CODE above the input box here on TT.

Always PREVIEW your post before submitting.

"just to move to the next occurrence"
You have to be a lot more specific.

---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

Here is one possible solution:

Option Explicit
Dim intStart As Integer

Private Sub txtAmount_Change()
intStart = 1
txtcmr.Text = ""
txtAccNo.Text = ""
End Sub

Private Sub cmdFind_Click()
Dim totRows As Long, i As Long

totRows = Worksheets("Sheet2").Range("C1").CurrentRegion.Rows.Count

If txtAmount = "" Then
   MsgBox "Enter Search Amount"
End If

For i = intStart + 1 To totRows
    If Trim(Sheet2.Cells(i, 3)) = Trim(txtAmount.Text) Then
        intStart = i
        txtcmr.Text = Sheet2.Cells(i, 2)
        txtAccNo.Text = Sheet2.Cells(i, 9)
        Exit For
    End If
    If i = totRows Then
         MsgBox "End of Search"
    End If
Next i

End Sub

FYI - if you are searching for the Amount that is in last row, you will find it along with the box "End of Search"


---- Andy

There is a great need for a sarcasm font.

 

[DJ2018]

perfect thanks and also advising how to show the code