Public Sub QtyEnter2()
Dim LookupValue as Variant, CodeRange As Range
Dim LastRow as Long, c as Range
LookupValue = ComboBox1.Value
LastRow = Range("A65536").End(xlUp).Row
Set CodeRange = Range("a4:a" & lastrow)
For Each c In CodeRange.Cells
If c.Value = LookupValue Then
c.Offset(0, 1).Value = TextBox2.Value * 1 ''?
End If
Next
End Sub

--
Jim
"Paul" wrote in message
...
| Hi there, I'm still pretty much a novice at this so please be gentle!
| I've been asked to automate the inputting into a stock file so I set up a
| very basic sheet first (The actual file has 7 inputted columns and will be
| run from a userform):
| A
| 1 5001 100
| 2
| 3
| 4 5000 0
| 5 5001 100
| .....
| 10 5006 0
|
| The macro I wrote was:
| Public Sub QtyEnter()
| Dim ThisCode, CodeRange As Range
| Set ThisCode = Range("a1")
| lastrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
| Set CodeRange = Range("a4:a" & lastrow)
| For Each c In CodeRange
| If c.Value = ThisCode.Value Then
| c.Offset(0, 1) = ThisCode.Offset(0, 1)
| End If
| Next
| End Sub
|
| Which works, so then I put a Combox(linked to the codes), a Textbox(for
the
| Qty) & Commandbutton with the code:
| Public Sub QtyEnter1()
| Range("A1") = ComboBox1.Value
| Range("B1") = TextBox2.Value * 1
| Call QtyEnter
| End Sub
|
| Which also works, so I tried combining the two:
| Public Sub QtyEnter2()
| Dim ThisCode, CodeRange As Range
| Set ThisCode = ComboBox1.Value
| lastrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
| Set CodeRange = Range("a4:a" & lastrow)
| For Each c In CodeRange
| If c.Value = ThisCode.Value Then
| c.Offset(0, 1) = TextBox2.Value * 1
| End If
| Next
| End Sub
|
| Which doesn't work (Object required at Set Thiscode) I know I Should be
| Dimming ThisCode as something but nothing seems to work.
| Help please!!
|
| Many thanks
| Paul
|