Concatenate a named range with text to one cell using in VBA?

[perola.rike]

I need a little code that merges a range of cells (A26:A100) named "NPresults" that contains text only, into the cell A26. I need . as delimiter, not comma.
I need this in VBA because it is part of a larger code.

Any ideas how to solve this?

For some hours now, I have looked but not found any solution, searching on concatenate formulas etc.

Best regards, Per-Ola Rike

 

[Firefly2012]

Hi

The following should work:

Dim vArr As Variant
Dim itm As Variant
Dim temp As String

vArr = Range("NPresults").Value

For Each itm in vArr
  temp = temp & "." & itm
Next itm

Range("B1")).Value = Mid(temp,2)

Obviously change destination to suit

 

[perola.rike]

Thanks for reply. I did not seem to work, I got a rumtime error 13, type mismatch. Seems like it's the "temp = temp& "." & itm" that wouldnt work...

Hi

The following should work:

Dim vArr As Variant
Dim itm As Variant
Dim temp As String

vArr = Range("NPresults").Value

For Each itm in vArr
  temp = temp & "." & itm
Next itm

Range("B1")).Value = Mid(temp,2)

Obviously change destination to suit

 

[Firefly2012]

Do you have error values in your named range?

Try changing that line to:

temp = temp & "." & CStr(itm)

 

[perola.rike]

In some way I managed to figure it out, so your code worked. I just had to tweak the other parts of my own code to fit in with yours.
Thank you!

Do you have error values in your named range?

Try changing that line to:

temp = temp & "." & CStr(itm)