VBA - Command button to run VLOOKUP formulae & also reset field button

[xxweelinseyxx]

Hi all, hoping someone can help me.

I have the VLOOKUP formulae prepared which works in a normal excel field (=VLOOKUP(Calc!$E$1,Calc!$B$2:$C$200,2,FALSE))

However, I want to add a command button, which when clicked will calculate the lookup formulae and put the result into Cell D10.

How do I do this?

Also - how do I create another command button which will effectively reset the same field D10 to blank(or zero) awaiting that the VLOOKUP button can then be ran again after that will override the blank/zero value.

Any help is appreciated.
Thanks
Linsey

 

[PhilS2520]

Hi Linsey,

Usually buttons are used to run VBA code, not to have a cell formula calculate. The cell will “automatically” calculate when the workbook or worksheet is changed. Mind you I have the word in quotations because you can set this behavior any way you want to, it’s default setting is automatic.

Can you be more specific why you want a button to activate a function on a worksheet, maybe we can provide you with a more elegant solution.

 

[xxweelinseyxx]

Hi Phil

It is for a club 'lottery type' monthly draw.

Basically each person pays a monthly charge to have their dedicated number in the draw. The paid up numbers are then entered into the draw (ie pasted into the 'Calc' worksheet with a second column attaching each number a random number using =rand and =rank functions.

Then on the 'Draw' worksheet, I have 6 blank cells/boxes. At present each of these 6 cells have a formula similar to (=VLOOKUP(Calc!$E$1,Calc!$B$2:$C$200,2,FALSE) in them, with a seperate command button which when clicked regenerates (ie recalculates the formula) 6 new lotto numbers in the 6 cells at once.

Howeve, what I have now been asked to do is have 6 separate buttons below each cell to generate the 6 lotto numbers separately (ie 6 different people can click togenerate a number to try replicate the old-style draw which involved 6 people drawing 6 balls from a tombola box).

Ultimately then a further button would be visible to 'Clear Draw' so that the 6 cells woul return to zero/blank so that the next draw is generated from blank cells and won't have numbers in them.

Hope this helps a little?

 

[Fluff]

How about

Sub Draw1()
Range("D10").Value = [VLOOKUP(calc!$E$1,calc!$B$2:$C$200,2,FALSE)]
End Sub

 

[xxweelinseyxx]

When i done this for some reason when the button is clicked it is calculating new numbers in all six cells and not just 'D10' ??

 

[Fluff]

Did you change the button to look at the code I provided?