if statement with array?? need some help please

[dasamoto]

Hi,

I am struggling with the syntax for the following:

I have a set of about 100 rows that are cell references to a previous page. Some of them are blank (by blank, I mean they have been set to "" because they do not meet the necessary requirement), and some of them have values. I am looking for a way to have one summary cell at the bottom that will just kind of "skip over" the cell if it is blank and just add the value if it comes through.

The following formula works but is tedious and not really a reasonable option for 100 separate formulas. Plus - i was curious as to what some of you guys had in mind.

Assume the column of values/blanks goes from B2:B100. I can use the formula:
=IF(B2="","",B2&", ")&IF(B3="","",B3&", ")

As you can see - this is the first two of the 100 cells.

Any suggestions would be greatly appreciated.

Much thanks,
Dan

 

[Scott R]

=SUM(B2:B100) should work since blanks or nulls evaluate as zero.

 

[dasamoto]

I should have been more clear - These are text values. As you see from the formula on the bottom, i want to include the text value and a comma for each cell that did not return the blank.

 

[Scott R]

Public Function NumberChain(cellRng As Range) As Variant

Dim celll As Range

For Each celll In cellRng
    If IsNumeric(celll) And celll.Value <> 0 Then
        NumberChain = NumberChain & celll.Value & ","
    End If
Next celll

If Len(NumberChain) = 0 Then
    NumberChain = ""
Else
    NumberChain = Left(NumberChain, Len(NumberChain) - 1)
End If


End Function

 

[dasamoto]

First off, thanks for taking the time Scott.

I am showing my inexperience here because I'm not exactly sure how to implement this formula to see if it works. I know that it's supposed to be in VBA, should i just put it into a module? If so, it says that it needs to be connected to a macro and i'm not very familiar with the public function.

Does the VBA response indicate that you're unsure of any way to do this in standard excel? Again, really appreciate your help.

-Dan

 

[Scott R]

Dan- My apologies as I was in a rush. Insert a module in your workbook and paste the custom function code. Your workbook now has to be saved as .xlsm instead of .xlsx (if Excel 2007). Now you can use the function anywhere in your worksheet, i.e. =NumberChain(B2:B100)

Somebody may be able to come up w/a pretty complicated formula to meet your needs, but it seemed to me to lend itself to VBA.