Combobox question

[Paul]

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

 

[Jim Rech]

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
| 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
|

 

[Paul]

Jim,
Many thanks, that didn't work (nothing happened) but by adapting it to:
Public Sub QtyEnter2()
Dim LookupValue As Integer, CodeRange 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

It works! Thanks for pointing me in the right direction
Paul