insert formula based on cell entry using vba

**jimb0693** · 10-05-2009, 03:16 PM

i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3

i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go

if i could get an idea what to start with i could probably figure out the rest but i'm stuck. any help appreciated

thanks

**Palmetto** · 10-05-2009, 04:24 PM

Maybe something along this line:

Please Login or Register  to view this content.

**jimb0693** · 10-05-2009, 11:12 PM

thanks for your help palmeto, worked a treat, i should be able to add the rest to it. thanks again

**jimb0693** · 10-06-2009, 02:22 AM

sorry i was a bit premature with this, comes from being awake in the middle of the night and not being thourough, the code is fine except it works only on the row specified, how would i extend it to run to the bottom of the worksheet?

**DonkeyOte** · 10-06-2009, 02:25 AM

Change

Please Login or Register  to view this content.

to be

Please Login or Register  to view this content.

Change the ROW number in the above as required (remove if not required) - ie above works on all of D with exception of changes made to D1 which it eg assumes header row

**jimb0693** · 10-11-2009, 03:44 AM

sorry but i'm really stuck with this now, i also need to change this line to run all the way down but everything i've tried has failed, help please!

Please Login or Register  to view this content.

**JBeaucaire** · 10-11-2009, 04:45 AM

Try this:

Please Login or Register  to view this content.

**royUK** · 10-11-2009, 05:16 AM

Is this what you want?

Please Login or Register  to view this content.

**jimb0693** · 10-11-2009, 05:30 AM

thanks for the help guys, i tried jbeaucaire's suggestion and it worked perfectly so, sorry royuk i didnt try yours, but the help is appreciated anyway, a huge THANK YOU to all.

**royUK** · 10-11-2009, 06:03 AM

That code only put's one formula in, you need the Select case to put in different formlas.

Also, the target.Count >1 will stop the code attempting to run if several cells are selected, and the Intersect part makes the code work only on Column D from D3 down.