string contra VBA code

  • Thread starter Thread starter dlotz
  • Start date Start date
D

dlotz

I had a string VBA code but I have lost it.

BAsicly I am trying to take several
rows of data combine them into one cell
with a ; and spce between each row once combines.
Pelase advise
 
Try this

Function BigConcat(data As Range, Optional Delimiter As String) As String
Dim c, str As String, temp As String
Dim l As Integer, addr As String, msg
Dim r As Long, col As Integer
Dim Alert As String

If Len(Delimiter) = 0 Then
Delimiter = ", "
Else: Delimiter = Delimiter
End If
' Check that data will display in cell and ext if not
For Each c In data
' Results can be rubbish if a cell in data range is empry
If Not IsEmpty(c) Then
l = Len(c) + l
r = c.Row
col = c.Column
If l > 1024 Then
addr = Cells(r, col - 1).Address
BigConcat = str
Alert = MsgBox("Value: " & temp & Chr(10) _
& "Cell " & Cells(r, col - 1).Address, , "Last cell Included in
Display!")
Exit Function
End If

If Len(str) = 0 Then
str = c
Else
str = str & Delimiter & c
End If

End If
Next c
BigConcat = Trim(str)
End Function


you can enter it =bigconcat(A2:A10) and it will use a comma between each cell.

or you can enter the delimiter of choice =BigConcat(A2:A10,";")

You could save the code in your Personal File then it will always be
available if you need it again.

If you have to create a personal file record a simple macro, save the file
as Personal. This is saved in Excel's Start up directory. You can see the
function under custom using the Function lists.

HTH
Peter
 
hmmmmm
I got an error meesage that took me here
Alert = MsgBox("Value: " & temp & Chr(10) _
& "Cell " & Cells(r, col - 1).Address, , "Last cell Included in


=BigConcat(H3:H979,"; ") this is formula I enter. does the space matter
So basiclly I copied and then went to the dev. tab
view code
insert module
paste the formula
ctrl S (save)
close out the code page
and tried to plug that sucker in
The brackets () went to bold and everything after typing the message
 
Here is my take on a macro to do what you want...

Function BigConcat(Data As Range, Optional Delimiter As String) As String
Dim X As Long
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _
Delimiter) & Delimiter
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function
 
maybe I am not saving the formula right.
Still get confused with VBA

I opened a brand to doc.
Copied and pasted the row that I want to considate in to one cell in colA
ok np
next went to the dev. tab
selected view code
right clicked the sheet
insert module
pasted the VBA code
ctrl S save, Saved as excel07 macro enab.
Close the VBA window

typed in b1 =bigconcat(highlighted the reange)
Then tried =bigconcat(a1:a1000,";")
hit enter and know I am geting #value in the cell

sorry guys,
 
A minor oversight on my part. Use this macro instead...

Function BigConcat(Data As Range, Optional Delimiter As String) As String
Dim X As Long, IR As Range
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
Set IR = Intersect(Data, Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data, Rows(X)))), Delimiter) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function
 
Two things... First, it's a UDF (User Defined Function), not a macro. Second, it seems I left out the default value for the Delimiter. Only the function declaration is changed, but I'm repeating the entire UDF to make it easier to copy/paste...

Function BigConcat(Data As Range, Optional Delimiter As String = ", ") As String
Dim X As Long, IR As Range
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
Set IR = Intersect(Data, Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data, Rows(X)))), Delimiter) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function

--
Rick (MVP - Excel)


A minor oversight on my part. Use this macro instead...

Function BigConcat(Data As Range, Optional Delimiter As String) As String
Dim X As Long, IR As Range
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
Set IR = Intersect(Data, Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data, Rows(X)))), Delimiter) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function
 
Okay, you may find this version of the function more flexible to use. The first version I posted *requires* a contiguous range of cells; this version will work with multiple, non-contiguous ranges; for example, something like this...

=BigConcat("; ",A1:A10,B2:H2,J3:M9)

The only problem (if this is even a problem) is that the Delimiter is no longer optional and there is no default for it (and it has been moved to the first argument position in order to allow an unspecified number of ranges to be passed into the function as a comma delimit list). Okay, here is the function code...

Function BigConcat(Delimiter As String, ParamArray Data()) As String
Dim X As Long, Z As Long, IR As Range
For Z = LBound(Data) To UBound(Data)
For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count - 1
Set IR = Intersect(Data(Z), Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data(Z), Rows(X)))), Delimiter) & Delimiter
End If
Next
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function

--
Rick (MVP - Excel)


A minor oversight on my part. Use this macro instead...

Function BigConcat(Data As Range, Optional Delimiter As String) As String
Dim X As Long, IR As Range
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
Set IR = Intersect(Data, Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data, Rows(X)))), Delimiter) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function
 
Given the BigConcat function is basically a "Join" function with delimiter,
I don't know how useful this variation on the last code I posted will prove
to be, but it allows you to inject text strings into the list. For example,
you can do this (in addition to using non-contiguous ranges in the list)...

=BigConcat("; ",A1:A10,"Some Text in the middle of the list",B2:H2,J3:M9)

Anyway, modifying the code to allow for this was relatively easy to do, so I
did it.<g> Here is the code which adds this feature into the mix...

Function BigConcat(Delimiter As String, ParamArray Data()) As String
Dim X As Long, Z As Long, IR As Range
For Z = LBound(Data) To UBound(Data)
If TypeName(Data(Z)) = "Range" Then
For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count - 1
Set IR = Intersect(Data(Z), Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data(Z), Rows(X)))), Delimiter) & Delimiter
End If
Next
Else
BigConcat = BigConcat & Data(Z) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function

--
Rick (MVP - Excel)


Okay, you may find this version of the function more flexible to use. The
first version I posted *requires* a contiguous range of cells; this version
will work with multiple, non-contiguous ranges; for example, something like
this...

=BigConcat("; ",A1:A10,B2:H2,J3:M9)

The only problem (if this is even a problem) is that the Delimiter is no
longer optional and there is no default for it (and it has been moved to the
first argument position in order to allow an unspecified number of ranges to
be passed into the function as a comma delimit list). Okay, here is the
function code...

Function BigConcat(Delimiter As String, ParamArray Data()) As String
Dim X As Long, Z As Long, IR As Range
For Z = LBound(Data) To UBound(Data)
For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count - 1
Set IR = Intersect(Data(Z), Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data(Z), Rows(X)))), Delimiter) & Delimiter
End If
Next
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function

--
Rick (MVP - Excel)


A minor oversight on my part. Use this macro instead...

Function BigConcat(Data As Range, Optional Delimiter As String) As String
Dim X As Long, IR As Range
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
Set IR = Intersect(Data, Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data, Rows(X)))), Delimiter) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function
 
Back
Top