Lost in Space

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
Can you write a macro that places a formula in a cell that provides a value off another cell?

Example: If column 1 ="John" then column 3 "=if("column 14" is blank, leave blank, if it has a number in it, subtract 4 from it and place the value in column 3).

Column 1 needs to be able to accept at least 3 names and each name will have a different number to subtract from column 14 if it has a number in it.

I'm sure its simple, however the more I look at it the more lost I become!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
Thanks,
It looks like I would need to write this formula 3 times in the macro and change the name each time, is that correct?
Also these names will be entered multiple times on the sheet, so would I need to leave off the 'cell' designation?
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
I missed reading that you need a macro and gave you a formula. Try the following codes.

Code:
sub IITg
x= cells(rows.count,1).end(xlUp).row
for a = 1 to x
for b = 1 to 3
c = choose(b,"john", "Rob","Kim")
d = choose(b,4,7,10)
if cells(a,1) = c and cells(a,14) <> "" then
cells(a,3) = cells(a,14) - d
endif
next b
next a
End sub
Run the macro.
If col A has names john or rob or kim and col N is not blank it will subtract 4 or 7 or 10 from col N and places in col C.
Ravi
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,397
Office Version
  1. 365
Platform
  1. Windows
i8ig

If you are looking to put a formula in column 3 (ie column C), try a formula like the one below. Note that the three names in the formula must be in alphabetical order.
=IF(N3="","",N3-LOOKUP(A3,{"Alan","John","Sam"},{12,4,8}))
 

Forum statistics

Threads
1,181,649
Messages
5,931,216
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top