Solved: A question about code finding a certain text

[venson0801]

Hello guys, I have been trying to create a inventory VBA userform for our company's inventory control. Because I am a beginner in EXCEL VB, so I couldn't find out how to debug the code I have wrote. My problem is that I have a "find" function to locate my current cell to the right position, if the numbers are in the sheet, then the code works fine; but if I inputted the non-existing value, then excel will show runtime error "91". I have tried reidentify my variables and still don't know what to do. It would be great if someone could see where the problem is, because I have been working on it for two days......Thank you so much, the below is the code:
Private Sub btnreceive_Click()

'for find last row
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
'for update list
Dim datatoFind2
Dim sheetCount2 As Integer
Dim counter2 As Integer
Dim currentSheet2 As Integer
Dim qtyM, qtynow As Long
Set ws = Worksheets("Receipts")

Sheets("Receipts").Select
'find first empty row in database

lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the database
With ws
Cells(lRow, 1) = txtkpn.Value
Cells(lRow, 2) = txtmpn.Value
Cells(lRow, 3) = txtmanufacture.Value
Cells(lRow, 4) = txtqty.Value
qtynow = txtqty.Value
Cells(lRow, 5) = txtsupplier.Value
Cells(lRow, 6) = txtPO.Value
Cells(lRow, 7) = txtby.Value
Cells(lRow, 8) = txtdate.Value
End With

'to find item in the master inventory sheet
Sheets("Master").Select
currentSheet2 = ActiveSheet.Index
datatoFind2 = txtkpn.Text
If datatoFind2 = "" Then Exit Sub
sheetCount2 = ActiveWorkbook.Sheets.Count
For counter2 = 1 To sheetCount2
Sheets(counter2).Activate


<here is where it got stock>
If IsError(Cells.Find(What:=datatoFind2, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate) = False Then Exit For
Next counter2


'add new item in master sheet
If ActiveCell.Value <> datatoFind2 Then
MsgBox ("ggg")

'update the quantity of current sheet
Else
ActiveCell.Offset(0, 4).Select
qtyM = ActiveCell.Value
ActiveCell.Value = qtyM + qtynow
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cbolocation.Value



End If


'to clear the information on the UI
Me.txtkpn.Value = ""
Me.txtmpn.Value = ""
Me.txtmanufacture.Value = ""
Me.txtqty.Value = ""
Me.txtsupplier.Value = ""
Me.txtPO.Value = ""
Me.txtby.Value = ""
Me.txtdate.Value = ""
Me.cbolocation.Value = ""
Me.cbotype.Value = ""

End Sub

[p45cal]

something like (untested):Set FoundCell = Cells.Find(What:=datatoFind2, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Not FoundCell Is Nothing Then
FoundCell.activate
else
Exit For
end if

[rbrhodes]

Hi,

I can't figure out what you are trying to do form your code but the error trap is ovbviously not working as written.

Try this instead:



Dim f

'<here Is where it got stock>
On Error Resume Next
f = Cells.Find(What:=datatoFind2, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If f = "True" Then Exit For

On Error GoTo 0
Next counter2



f will be "True" if found and false if not

[venson0801]

Hi rbrhodes,
Your code actually works! I modified some of it but now it is functioning the way I wanted it! Thank you guys so much!