Insert formula from VBA into varying cells

[McDuffieB]

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

 

[mikerickson]

...
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]

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]

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...

 

[McDuffieB]

Sir

It still returns the values for row 9....

Thanks again Billy