Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a named formula in VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a named formula in VBA
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a named formula in VBA
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. "Joel" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a named formula in VBA
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. "JHARRIS133" wrote: 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. "Joel" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a named formula in VBA
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]" "JHARRIS133" wrote: 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. "Joel" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Named ranged for each column... | Excel Programming | |||
adding worksheet name to a named range with VBA? | Excel Programming | |||
adding named ranges | New Users to Excel | |||
Adding a named range to a cell | Excel Programming | |||
Adding to a named range on another worksheet | Excel Programming |