Function to Add Cells if name exists

[saschmeling]

I am working on a VBA program and have several VBA projects that create cell names if the area exists. For example- I filter data from one sheet to another based on a category number.

At the end of that data set I complete several Sum functions to sum the data above.
The sum data cells are then named for example PVSAL.

This is repeated for each category, pasting it below the category above.

Each Sum cell is given its own name.

Now I need a formula that will sum up all of the category totals. I was using a simple formula for example.

Selection.FormulaR1C1 = "=PYSAL+PYSVC+PYOTH+PYFIX"

However, if the name was not created because the category did not exist- I get a Name Error. Any idea how I can write this as an If statement.

Something like:

If PYSAL Name exists then Add it else add 0
If PYSVC Name exists then Add to PYSAL else add 0
If PYOTH Name exists then add to PYSAL else add 0
If PYFIX Name exists then add to others else add 0

I will take out all sensitive information from the spreadsheet and post it if it will help- but need to remove the information first.

Thanks,

 

[JoeMo]

Try this:

Sub test()
Dim myNames As Variant, strNames As String
myNames = Array("PYSAL", "PYSVC", "PYOTH", "PYFIX")
For i = LBound(myNames) To UBound(myNames)
    If NameExists(CStr(myNames(i))) Then strNames = strNames & "+" & CStr(myNames(i))
Next i
Selection.Formula = "=" & Right(strNames, Len(strNames) - 1)
End Sub
Function NameExists(Nam As String) As Boolean
Dim nm As Name
For Each nm In ActiveWorkbook.Names
    If nm.Name = Nam Then
        NameExists = True
        Exit Function
    End If
Next nm
End Function

 

[saschmeling]

Thanks Joe-

Worked good.