Code help using Find method

[longhair]

I'm working on a project & can't seem to work out some of the logic...any suggestions would be appreciated.
I have an excel spreadsheet that contains 3 columns of data. Column "A" is part#, column "B" is lot# and column "C" is last number used. I'm currently using 3 input boxes to get info from the user. I then want to find the part# and lot# get the last number used and transfer this to another sheet, then increase the last number used by my last input box. Here is the code I have so far (the sticking point has been the .Find I believe):

Sub lotnum()

Dim lotnum As Double
Dim qty As Integer
Dim part As String
Dim foundpart As String
Dim oFoundpart As Object
Dim oFoundlot As Object
Dim message1, title1
Dim message2, title2
Dim message3, title3

message1 = "Enter part number being shipped:"
title1 = "part input"
part = InputBox(message1, title1)
message2 = "Enter the lot number to be used:"
title2 = "lotnum input"
lotnum = InputBox(message2, title2)
message3 = "Enter the number of boxes being shipped:"
title3 = "qty input"
qty = InputBox(message3, title3)
Sheets("Lot&quot.Select
With Worksheets("Lot&quot.Range("A:A&quot
Set oFoundpart = .Find(part, LookIn:=xlValues)
If Not oFoundpart Is Nothing Then
foundpart = oFoundpart.Address
Range(oFoundpart).Select
Range(oFoundpart).Activate
'With Worksheets("Lot&quot.Range("b:b&quot
' Set oFoundlot = .Find(lotnum, after:=(foundpart - 1))
' If Not oFoundlot Is Nothing Then
' foundlot = oFoundlot.Address
' Range(oFoundlot).Select
' Range(oFoundlot).Select
' End If
'End With
End If
End With
End Sub

Any ideas would be much appreciated. Thanks in advance.

Longhair

 

[xlbo]

I think it's just that your syntax is a bit screwy - here's what I use syntax wise :


Set foundcell = Worksheets("Users&quot.Columns("A&quot.Find(UserName)

--- nb above is all one line

If foundcell Is Nothing Then
--- do stuff
Else
--- do other stuff
End If

HTH
Geoff