How to create a textjoin worksheet function with dynamic range

Question

I have data where I have many column headers. One of the header is "Text" and one other header is "Value Date". I want to combine the values contained in every row between these columns in another column row-wise.

The problem is the number of columns between these two headers is not constant. It changes with every new ledger I export. So I want my code to be dynamic in such a way that it will identify the column of "Text" and then it will identify the column of "Value Date" and combine everything between in another column row-wise.

This is where I have reached with my code but I don't know why it's not working. I have been trying this for last 3 days only to get nowhere. When I run this code, the result which I get is "TextColumnNo:ValueColumnNo".

Sub TextJoin()

Dim TextColumnNo As Range
Dim ValueColumnNo As Range

Range("A1").Select
ActiveCell.EntireRow.Find("Text").Activate
Set TextColumnNo = Range(ActiveCell.Address(False, False))
Range("A1").Select
ActiveCell.EntireRow.Find("Value").Activate
Set ValueColumnNo = Range(ActiveCell.Address(False, False))
ActiveCell.Offset(1, -1).Select
Application.CutCopyMode = False
ActiveCell.Value = Application.WorksheetFunction.TextJoin(" ", True, _ 
"TextColumnNo:ValueColumnNo")
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8524")
ActiveCell.Range("A1:A8524").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

I recommend you to read: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) which is extremely helpful for beginners. — Pᴇʜ, Apr 16 '18 at 10:59
I read that. Thanks for your recommendation. In future I will try to avoid any select or active_ to make my codes more efficient. — Mayank, Apr 16 '18 at 13:17

Harassed Dad · Answer 1 · 2018-04-16T11:12:14.383

Sub TextJoin()


Dim ColRefText As Long
Dim ColRefValueDate As Long
Const firstcol = "Text"
Const secondcol = "Value Date"
Dim r As Range
Set r = Rows(1).Cells.Find(firstcol)
If Not r Is Nothing Then
  ColRefText = r.Column
    Set r = Rows(1).Cells.Find(secondcol)
    If Not r Is Nothing Then
         ColRefValueDate = r.Column
    End If
End If
If ColRefValueDate + ColRefText > 0 Then
With Cells(2, Worksheets(1).Columns.Count).End(xlToLeft).Offset(0, 1)
    .Formula = Replace("=" & Cells(2, ColRefText).AddressLocal & "&" & Cells(2, ColRefValueDate).AddressLocal, "$", "")
    .Copy Range(.Address, Cells(ActiveSheet.UsedRange.Rows.Count, .Column).Address)
End With
End If
End Sub

score 1 · Accepted Answer · answered Apr 16 '18 at 10:56

1

You would need 2 loops for this. One looping through all rows and one looping through the columns to combine the text for each row.

Note that you need to adjust some things like sheet name and output column here.

Option Explicit

Public Sub TextJoin()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1") 'define a worksheet

    'find start
    Dim FindStart As Range
    Set FindStart = ws.Rows(1).Find("Text")
    If FindStart Is Nothing Then
        MsgBox "start not found"
        Exit Sub
    End If

    'find end
    Dim FindEnd As Range
    Set FindEnd = ws.Rows(1).Find("Value Date")
    If FindEnd Is Nothing Then
        MsgBox "start not found"
        Exit Sub
    End If

    'find last used row in column A
    Dim lRow As Long
    lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row


    Dim iRow As Long
    For iRow = 2 To lRow 'loop through all rows (2 to last used row)
        Dim CombinedText As String
        CombinedText = vbNullString 'initialize/reset variable

        Dim iCol As Long 'loop through columns for each row (from start to end column)
        For iCol = FindStart.Column To FindEnd.Column
            CombinedText = CombinedText & ":" & ws.Cells(iRow, iCol).Text 'combine values
        Next iCol

        ws.Range("Z" & iRow) = CombinedText 'write values in column Z
    Next iRow
End Sub

answered Apr 16 '18 at 10:56

Pᴇʜ

56,719
10
49
73

Hi Peh! Your code works perfect. I made one change in your code to make it perfect for my use. `Set FindEnd = ws.Rows(1).Find("Value Date").Offset(0, -1)` . I added `Offset(0,-1)` in this line as I wanted to combine values of columns before the "Value Date". I have just one more thing to ask which will completely sort out my problem. Currently, Column "Z" is hard coded in for the CombinedText value to appear but I want that the CombinedText shall appear in the column immediately left to "ValueDate" as Column Z may contain different values in different sheets. Your help is appreciated. – Mayank Apr 16 '18 at 13:03
@Mayank but your version will throw an exception if "Value Date" is not found. Better to leave that `Set` line as it was and change it here `For iCol = FindStart.Column To FindEnd.Column - 1`. • And you can change `ws.Range("Z" & iRow) =` into `ws.Cells(iRow, FindEnd.Column - 1) = ` to write into the column before "ValueDate". – Pᴇʜ Apr 16 '18 at 15:42
@Peh thanks. Now its working perfectly fine. I learned a lot from understanding your code. – Mayank Apr 17 '18 at 11:24
@Mayank Then please mark this answer as solution. So everybody knows that this is solved. – Pᴇʜ Apr 17 '18 at 11:49
@Peh Sorry! I didn't know about that option. – Mayank Apr 19 '18 at 12:39

How to create a textjoin worksheet function with dynamic range

2 Answers2