This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have written a rather long formula, so I had to cut it part.
However, this doesn't work as my answer is "#NAME?" in excel, and the formula is : "=IF(SUM((WWW))=0,""-"",SUM((WWW)))"
What am I doing wrong? The formula works if remove the "=" in it, and put this in place manually in excel.
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPart3 As String
Dim theFormulaPart4 As String
Dim theFormulaPart5 As String
theFormulaPart1 = "=IF(SUM((WWW))=0,""-"",SUM((WWW)))"
theFormulaPart2 = "IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""EAZA""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+XXX"
theFormulaPart3 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""Temporary""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+YYY"
theFormulaPart4 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""Associate""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+ZZZ"
theFormulaPart5 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""N-EEP""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1)"
With ActiveSheet.Range("I42")
.FormulaArray = theFormulaPart1
.Replace "WWW", theFormulaPart2, xlPart
.Replace "XXX", theFormulaPart3, xlPart
.Replace "YYY", theFormulaPart4, xlPart
.Replace "ZZZ", theFormulaPart5, xlPart
End With
Furthermore, the set-up seems to work fine:
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPart3 As String
Dim theFormulaPart4 As String
Dim theFormulaPart5 As String
theFormulaPart1 = "=IF(SUM(WWW)=0,""-"",SUM((WWW)))"
theFormulaPart2 = "3+XXX"
theFormulaPart3 = "4+YYY"
theFormulaPart4 = "6+ZZZ"
theFormulaPart5 = "7"
With ActiveSheet.Range("I36")
.FormulaArray = theFormulaPart1
.Replace "WWW", theFormulaPart2, xlPart
.Replace "XXX", theFormulaPart3, xlPart
.Replace "YYY", theFormulaPart4, xlPart
.Replace "ZZZ", theFormulaPart5, xlPart
End With
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
Well, thank you very much anyway!
Than at least I know I've tried everything before solving this one in a slightly less elegant way :)
Aargh - it's back to the original problem: the formula is too long (FormulaArray cannot be set to a string of more than 255 characters, although Excel itself accepts much longer formulas). I'm afraid I don't see a solution except using helper cells with parts of the formula as a workaround.
Thanks so much for the reply!
Unfortunately this didn't do the trick :(
I get the following error message: "Unable to set the FormulaArray property of the Range class"
Try
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPart3 As String
Dim theFormulaPart4 As String
Dim theFormulaPart5 As String
theFormulaPart1 = "=IF(SUM((WWW))=0,""-"",SUM((WWW)))"
theFormulaPart2 = "IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""EAZA""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+XXX"
theFormulaPart3 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""Temporary""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+YYY"
theFormulaPart4 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""Associate""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1))+ZZZ"
theFormulaPart5 = "(IF(FREQUENCY(IF((Scientific_Name=RC[-8])*(Total>0)*(Membership=""N-EEP""),MATCH(Mnemonic,Mnemonic,0)),MATCH(Mnemonic,Mnemonic,0))>0,1)"
theFormulaPart1 = Replace(theFormulaPart1, "WWW", theFormulaPart2)
theFormulaPart1 = Replace(theFormulaPart1, "XXX", theFormulaPart3)
theFormulaPart1 = Replace(theFormulaPart1, "YYY", theFormulaPart4)
theFormulaPart1 = Replace(theFormulaPart1, "ZZZ", theFormulaPart5)
With ActiveSheet.Range("I42")
.FormulaArray = theFormulaPart1
End With