Your code gives me the same results as 9.2 and not 9.20. Thanks for your help.


Art.



"Dave Peterson" wrote:

I dropped your test for 0.

Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
if vartemp.value < 0 then
JoinString = JoinString & varDelimiter & varTemp.Text
end if
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function

Dave Peterson wrote:

If you've the cells to be concatenated are formatted nicely, you could use this:

Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
JoinString = JoinString & varDelimiter & varTemp.Text
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function

art wrote:

Thanks. Can you please also modify the code so it gives you all the amount in
a number format like 9.00 and not 9?

Thanks.

Art

"Jacob Skaria" wrote:

Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
If varTemp < 0 Then
JoinString = JoinString & varDelimiter & varTemp
End If
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

I got a response from "Jacob Skaria":

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.



--

Dave Peterson

--

Dave Peterson