none
Macro to find and replace a value RRS feed

  • Question

  • Hi all

    I have a spreadsheet that stores information about seminars. One sheet holds names, dates and capacity of all the running seminars. (Capacity is always 250 for each one).

    On the next sheet I need to book people in to the relevant seminar, and then this should happen...

    When I click my confirm button, it should go and find the relevant seminar in the seminar sheet, find the relevant 250 and update it by subtracting the number of people I am booking in.

    E.G.

    Sheet 1 - details of seminars (name, date, capacity)

    Sheet 2 - allows me to choose a seminar using a dropdown, then I choose the number* of people attending. When I click confirm it should take that number* and subtract it from the relevant capacity in Sheet 1.

    I know roughly how to do it with a VLOOKUP, but getting the VLOOKUP to action when the button is clicked is beyond me.

    Any help would be much appreciated.

    Thanks.


    Thursday, February 12, 2015 10:52 PM

Answers

  • Hi sunnycorleone,

    >>When I click my confirm button, it should go and find the relevant seminar in the seminar sheet, find the relevant 250 and update it by subtracting the number of people I am booking in.<<

    According to the desciption, you want to find the exact cell based on the requirment. If you want to use the VLookUp in macro, we can use WorksheetFunction Object which used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic. And here is a sample that for your reference:

    Debug.Print Application.WorksheetFunction.VLookup("FindValue", Range("C1:D8"), 2, False)

    Also we can use Range.Find to find method to find specific information in a range. This example finds all cells in the range A1:A500 on worksheet one that contain the value 2 and changes it to 5:

    With Worksheets(1).Range("a1:a500") 
        Set c = .Find(2, lookin:=xlValues) 
        If Not c Is Nothing Then 
            firstAddress = c.Address 
            Do 
                c.Value = 5 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing And c.Address <> firstAddress 
        End If 
    End With
    

    Also if you have issues about Excel object model, we can also use record macros to familar with it.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 16, 2015 2:02 AM
    Moderator