string contra VBA code
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
"Billy Liddel" wrote:
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
" wrote:
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
|