Finding a record based on user input

[Soundman]

Hi all,

I'm a newbie, so please be gentle

Here's my project.
I've got a sales spreadsheet that I've got one user form that add
initial data
(item#, qty, make, model, cost) to consecutive rows. I have given the
constant Item
numbers in column A # 1-120. This part works great. I'm trying to get
new
user form to go back & add actual cost & shipping into new column
based on
what item number the user types into a text box in the same user for
as the
additional data.

IE: I have items 1 - 5 & want to add actual cost & shipping to item 3
The user
would pop up the form from a button with a macro & the fields would as
for
item#, actual cost & actual shipping. The user inputs this info & i
finds the
correct item & adds the new info.

I'm sure this is pretty basic, but I don't know how to make it find th
correct
item# .

Can anyone help?

Thanks!!

 

[Guest]

sounds like your question is how to find the row number that contains the
item number that was input.

One way to do that:


Sub test()
Const x As Byte = 3
Const Make As String = "XYZ"
Const Model As String = "AB100"
Const Cost As Long = 500
Dim Row1 As Long

Row1 = Application.Match(x, Sheet1.Range("A:A"), 0)
With Sheet1
.Cells(Row1, 2).Value = 100
.Cells(Row1, 3).Value = Make
.Cells(Row1, 4).Value = Model
.Cells(Row1, 5).Value = Cost
End With

End Sub


where x = the item number input by the user. also, I should point out I am
using the code name for the worksheet. you may need to change it for your
spreadsheet.

 

[Soundman]

Thanks for the help, but I'm not understanding how to implement thi
code

 

[Guest]

Sorry. I had assumed you had set up a userform. Also, I misread - you have
the quantity, make, model and wish to add cost and shipping to new columns.

Assuming you want to add Cost to column 5 and shipping to column 6, the
itemnumber is in column A, this uses inputboxes to get the info from the user
and should put it in columns 5 and 6 of the proper row.

You'll need to insert a VBA code module into your workbook. Hit Alt-F11,
Find your workbook in the Project Explorer window and select it. Click
Insert/Modules. Copy and paste this macro into the Code Window that appeared
when you inserted the module.

You will need to verify this line of the macro is correct:

With Sheet1

To do this, make sure your control toolbar is visible (View/Toolbars/Control
Toolbox). Make sure the worksheet containing you data is the active sheet
and click on the properties button of the control toolbar (s/b second from
left). This will show the properties of the worksheet. The first one (Name)
is the code name for your worksheet and may or may not be the same as the
name on the tab of your worksheet (many folks use the code name instead of
the tab name - because it is less likely to be changed and cause the macro to
not find the worksheet). If the code name is not Sheet1 then change Sheet1
in the code to match whatever yours is.

Also if you need Cost and Shipping in columns other than 5 and 6, change the
constants CostCol and ShippingCol to whatever you need.

If you have not created the button, use the forms toolbar, click on the
button then "draw" the button on your worksheet. Excel should prompt you to
assign a macro to it. If not you should be able to right click on it and
select "assign macro".



Sub InputData()
Const CostCol As Integer = 5
Const ShippingCol As Integer = 6
Dim ItemNumber As Long
Dim Cost As Currency
Dim Shipping As Currency
Dim Row1 As Long

On Error GoTo ExitProgram

ItemNumber = InputBox("Enter Item Number:")
Cost = InputBox("Enter Item Cost:")
Shipping = InputBox("Enter Shipping Amount:")
Row1 = Application.Match(ItemNumber, Sheet1.Range("A:A"), 0)

With Sheet1
.Cells(Row1, 4).Value = Cost
.Cells(Row1, 5).Value = Shipping
End With

Exit Sub
ExitProgram:
MsgBox "An error occurred. Please check item number and try again."

End Sub

 

[Soundman]

Thank you again for the help. I'll see how this goes. I would also b
happy to forward what I have so far if you're interested. I just nee
to know where to email it.

Thanks again. I'll try it ou

 

[Guest]

If you don't get the results you need, you can email it to me at

(e-mail address removed)

I will not be able to look at it til next week (away from home and cannot
download things to company computer).