Insert formula from VBA into varying cells

McDuffieB

New Member
Joined
Jul 9, 2011
Messages
4
Team

I am attempting to enter the following formula using a define named or function from vba in Column J on multiple worksheets. The formula is located in about 200 locations through the workbook.

=IF(AND($C9="Red Cart",dnLocality_Rate<(VLOOKUP($D9,dnSupplement,2,FALSE))),($E9+(VLOOKUP($D9,dnSupplement,2,0)*$E9))*$F9+($H9*$I9),($E9+($E9*dnLocality_Rate))*$F9+($H9*$I9))/$B$3

Know Factors:
--the column is always Column J
--the row changes so I would need help modifying the formula to be able to recognize the specific row that the formula was on.

I would perfer to just call a function or a define name range for the formula so that when the formula is updated I do not have to physically update every location but hopefully a single location that in turn would keep the sheets updated.

Thus on the individual worksheets I could put something like =definednameformula or call a procedure.

Any suggestions are most appreciated. Thanks

Billy
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,781
...
I would perfer to ... define name range for the formula so that when the formula is updated I do not have to physically update every location but hopefully a single location that in turn would keep the sheets updated.

Thus on the individual worksheets I could put something like =definednameformula....

That sounds like a good idea. What problems are you having with it?
 

McDuffieB

New Member
Joined
Jul 9, 2011
Messages
4
Sir

When I use the "=definednameformula" I have been unsuccessful in getting the formula to update to the correct row.

Example:

When I enter =dnFormula in J9 the formula reads =IF(AND($C9="Red Cart",dnLocality_Rate<(VLOOKUP($D9,dnSupplement,2,FALSE.....

The problem is when I enter =dnFormula in J10 the formula reads =IF(AND($C9="Red Cart",dnLocality_Rate<(VLOOKUP($D9,dnSupplement,2,FALSE.....

I need it to update the row so that it would read =IF(AND($C10="Red Cart",dnLocality_Rate<(VLOOKUP($D10,dnSupplement,2,FALSE.....

Thanks Billy
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,781
Named ranges, values and formulas follow the standard rules for absolute/relative referencing.

Select J9 and then define the name
Name: myFunction
RefersTo: =IF(AND($C9="Red Cart",dnLocality_Rate<(VLOOKUP($D9,dnSupplement,2,FALSE.....

When you press ADD, the Define Name routine will add the worksheet name*.

putting =myFunction in J9 should return the desired result.
Then select J10 and look at the name manager, myFunction should refer to
=IF(AND(Sheet1!$C10="Red Cart",dnLocality_Rate<(VLOOKUP(Sheet1!$D10,dnSupplement,2,FALSE.....
and =myFunction should return the appropriate result.

*-If you need to make it relative to sheet you can do that by ommitting the sheet name in the RefersTo, but not the sheet indicator
i.e. =IF(AND(!$C9....
rather than =IF(AND(Sheet1!$C9...
 

Watch MrExcel Video

Forum statistics

Threads
1,109,120
Messages
5,526,933
Members
409,730
Latest member
mimipiz

This Week's Hot Topics

Top