# Macro to find and replace a value

• ### 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

• 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
Do
c.Value = 5
Set c = .FindNext(c)