Posted By

BDOGG32 on 04/01/15


Tagged

code function excel join vba


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

BDOGG32


Join Function


 / Published in: Visual Basic
 

Allows you to concatenate a range of cells with the optional connector string. For example you have a list of names from A1:A10 that you want to concatenate as well as add a comma and a space you can type this into a cell: =JOINS(A1:A10, ", ") and this will concatenate all the 10 cells instead of using the built in concatenate function that doesn't accept an array of ranges which means you have to select each one, one at a time in the formula like: =CONCATENATE(A1,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6,", ",A7,", ",A8,", ",A9,", ",A10)

  1. Public Function JOINS(vA As Variant, Optional sConnector As String) As String
  2. Dim elem As Variant
  3. Dim sTmp As String
  4.  
  5. 'Application.Volatile (True)
  6.  
  7. On Error GoTo ErrorHandler
  8.  
  9. For Each elem In vA
  10. If Len(elem) Then
  11. sTmp = sTmp & sConnector & elem
  12. End If
  13. Next
  14. If Len(sConnector) Then sTmp = Mid$(sTmp, Len(sConnector) + 1)
  15.  
  16. JOINS = sTmp
  17. Exit Function
  18. ErrorHandler:
  19. JOINS = CVErr(xlErrValue)
  20.  
  21. End Function

Report this snippet  

You need to login to post a comment.