venson0801
07-13-2010, 04:25 PM
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
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