Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox question
Hi there, Im still pretty much a novice at this so please be gentle!
Ive 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 doesnt 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox question
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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox question
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 "Jim Rech" wrote: 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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
another combobox question | Excel Programming | |||
combobox question | Excel Programming | |||
Combobox question | Excel Programming | |||
Combobox Question | Excel Programming | |||
combobox question | Excel Programming |