Macro Needed - Should be easy

heather

New Member
Joined
Apr 8, 2002
Messages
8
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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top