Macro Needed - Should be easy
Macro Needed - Should be easy
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Macro Needed - Should be easy

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can find some recommended books here:
    http://www.mrexcel.com/book.shtml
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com