Loop non contingnius cells with vba function

[tryagain]

This function is only a cimple eks. in real world it count specifik char's in specifik cells

Function test(rng)
For Each c In rng
test = test & c
Next
End Function

The function works ok when i do =test(A2:A9)

but if i want to mark non contingnius cells like =test(A2;A4;A6)
using mouse and CTRL together the code fails Why ?

OBS: i am selecting cells with mouse like =test( and then select cpecifik cells )
and the semicolons in eksamble is sepperators in DK-Excel
i tryed komma and column - same result

thanks in advance

 

[Eric W]

Putting the separators like that makes the function think that you are passing multiple parameters, and it only has one defined. You have 2 options:

First, you can use your function as is, but when you enter the function on your worksheet, enter it like this:
=test(( ... then click on your cells ... ))
The extra parentheses tell it that the whole range is one parameter.

Second, you can use a paramarray in the function to accept a variable number of parameters:

Public Function test1(ParamArray rng())
Dim c As Range, i As Long


    For i = LBound(rng) To UBound(rng)
        For Each c In rng(i)
            test1 = test1 & c
        Next c
    Next i
    
End Function

Hope this helps!

 

[tryagain]

Ohh yeaa santa Eric your the man, both solusions worked greeeeeat thanks alot

 

[Eric W]

You're welcome.