Macro Needed - Should be easy

[heather]

Two sheet workbook. Sheet A is a form that fills in customer information based on a drop down box that they select from (cell J4). The drop down box is just a customer number and depending on what they select it fills in the detail - customer name, etc. The user has one blank field in the form to fill in. It's a date (cell c14).

I want a macro that will look at the drop down box (sheetA!J4) to see what they selected and then go to Sheet B and find that customer number (number is listed in column B [a whole bunch of them]) and then place the date in column C next to the right customer number.

I want a macro to do this since I have a macro that prints out an agreement based on the customer and would like to combine it with my other so that the user has it easy.

Can someone help? I have gotten a kick out of VBA and am very green but have had fun learning some of it. I plan to purchase a book this weekend to help with the rest of the project. Thanks!

[Al Chara]

Try the following:

Private Sub ComboBox1_Change()
With Worksheets("SheetB").UsedRange
Set c = .Find(ComboBox1.Value, LookIn:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("SheetB").Range("C" & c.Row).Value = Range("c14").Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

Edit as needed.

[heather]

Thanks Al, I will give it a try. I didn't make it to the bookstore this weekend, but do you have a recommendation on a good VBA book?

[Al Chara]

You can find some recommended books here:
http://www.mrexcel.com/book.shtml

[heather]

I am struggling with making this work. It's stumped on the Combo Box. I have tried to rewrite and combine it with another, but still can't get it to work. It is mostly because I don't understand the language.

If I remove the combo box from the theory, and said the value is is J14 how would that change the code?

Here's the scenario:
Sheet A is a form that fills in customer information based on the customer number they type in cell J14. The user also puts a date in Sheet A in cell C14. I want to have a macro (on the push of a button, not automatically) look at the customer number in J14 and find that number on Sheet B (cust. numbers are listed in column C) and then put the date that they typed in on SheetA!c14 one column over from the match to the customer number on Sheet B.

Arrgh!

[Al Chara]

See Below for comments on what the macro does:

Private Sub ComboBox1_Change()
'Sets where and what to look for
With Worksheets("SheetB").UsedRange
Set c = .Find(ComboBox1.Value, LookIn:=xlWhole)
If Not c Is Nothing Then 'This line is the same as saying If c <> Nothing Then
firstAddress = c.Address
'sets up the loop to find all cells that are equal to the find value (combobox1.value)
Do
Worksheets("SheetB").Range("C" & c.Row).Value = Range("c14").Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

This code should go in the sheet module that houses your combobox. Post any further questions.

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-16 06:39 ]