Adding a named formula in VBA

[JHARRIS133]

Hello,

I have a worksheet that has a named formula that takes various arguments
from the current row and performs a function. This is for an app that
consolidates multiple status reports into a master report, so I need to have
different names for the function, one for the master version, and one for the
source version. The master version of this formula will always exist in the
spreadsheet, but I would like to dynamically create the other version, based
on what the master version refersto.

Here is my formula:
=getStatus('Status Report'!$K15,'Status Report'!$L15,'Status
Report'!$E$9,'Status Report'!$P15,'Status Report'!$R15)

The master version of this formula is called CGIStatusColor, and the new
version I am trying to create is called CustStatusColor, but it should take
exactly the same arguments. Here is the code I use to create this new named
formula:

For Each c In rngStatusColors.Cells
wksStatusReport.Range("J15").Select
Dim nm As Name
For Each nm In ThisWorkbook.Names
With nm
If (Left(nm.Name, 3) = "CGI") Then
MsgBox nm.RefersTo
ThisWorkbook.Names.Add Name:="CustStatusColor",
RefersTo:=nm.RefersTo
End If
End With
Next nm
c.Formula = "=CustStatusColor"
Next c

As you can see from the msgbox line above, I've looked at the RefersTo
formula just prior to assigning it, and it is correct. However, when I check
the RefersTo in the Define Name box, here is what is actually entered:

=getStatus('Status Report'!$K29,'Status Report'!$L29,'Status
Report'!$E$9,'Status Report'!$P29,'Status Report'!$R29)

I cannot figure out why the row references are changing to be row29 rather
than row15, as they are immediately before I assign them.

Please help!
Thanks.

 

[Joel]

Did you ever ask the question why the $E$9 didn't change rows???


Put a dollar sigh infront of the 15 and it wil solve your problem!!!!!!!!!!

 

[JHARRIS133]

This formula needs to get down rows, so I cannot use an absolute reference to
any of the arguments with the exception to $E$9, which will stay constant.
That was the reason why I didn't do them all like that.

 

[JHARRIS133]

Actually, I think I just figured it out. Because the formula needs to use
relative cell references starting from K15, this is the cell I selected
before assigning the formula. However, when I removed that select statement,
it seems to work fine now.

 

[Joel]

The code c.Formula = "=CustStatusColor" is smart enough to know that you are
copying a formula to a new row and is making the changes just like performing
a copy on the worksheet.

You may want to use R1C1 definitions where you can use the current row
"='Status Report'!R[]C[11],'Status Report'!R[]C[12],'Status
Report'!R[9]C[5],'Status Report'!R[]C[16],'Status Report'!R[]C[18]"