Using a macro to locate a user given value

[Chip Smith]

Essentially I am having a problem with a macro, in that I created a Userform.
Data put into the first txt box will in turn be used to look up that
particular value in my spreadsheet, column K to be exact, once it finds the
value that was inserted in the first txt box in the user form, it then
recalulates a previous entry. heres the macro thus far....

Private Sub cmdAdd_Click()
Dim Order As Boolean
Dim Index As Variant
Dim nextrow As Long
Dim myRng As Range

Order = Me.txtOrder.Value

Set myRng = Range("a:k")

nextrow = Range("k65536").Row + 1

Index = Application.Match(Order, myRng, 0)

If IsError(Index) Then
MsgBox "Not Found, check Order No. and try again"
Else
myRng(Index).Select
ActiveCell.Offset(0, -9).Value = Cells.Value + (Me.txtShip.Value /
Me.txtPart.Value)
End If

Me.txtOrder.Value = ""
Me.txtShip.Value = ""
Me.txtPart.Value = ""
Me.txtOrder.SetFocus

End Sub

 

[Tom Ogilvy]

your use of Cells is flawed. I will assume you want to add the calculated
value to the offset cell.

Private Sub cmdAdd_Click()
Dim Order As Boolean
Dim Index As Variant
Dim nextrow As Long
Dim myRng As Range

Order = Me.txtOrder.Value

' Only looking in column K, so only look in column k
Set myRng = Range("k:k")

nextrow = Range("k65536").Row + 1

Index = Application.Match(Order, myRng, 0)

If IsError(Index) Then
MsgBox "Not Found, check Order No. and try again"
Else
myRng(Index).Select

ActiveCell.Offset(0, -9).Value = ActiveCell _
.Offset(0,-9).Value + (Me.txtShip.Value / _
Me.txtPart.Value)
End If

Me.txtOrder.Value = ""
Me.txtShip.Value = ""
Me.txtPart.Value = ""
Me.txtOrder.SetFocus

End Sub

 

[Chip Smith]

You are correct in the part of adding to the cell value, however i originally
had my formula and range set the same as you suggested, but inturn on my
testing i get the same problem with the msg box error. and i typed it in
exactly. the variant i'm using to search for is a 8 digit number....

 

[Tom Ogilvy]

Why is the search term dim'd as Boolean if you are searching for a number?

the value coming from a textbox is a string. As an illustration, the string
"123" does not match the number 123 in the eyes of the match function. so:


Change

Index = Application.Match(Order, myRng, 0)

to
Dim Order as String
Index = Application.Match(clng(Order), myRng, 0)