Forum Discussion

TimothySmith's avatar
TimothySmith
Copper Contributor
Jan 10, 2022

TextJoin using vba when a new row is filled

I am new to programming in excel (I mainly work in Access) and am running into a bit of trouble. My company tracks performance metrics for each employee twice a month and a report is required to push to corporate monthly. Currently everything is done by hand or copy and paste and I am trying to make my future additional duty more simplified, so I am creating an excel db from scratch.  I have a table that has 3 columns (I will be adding the rest of the columns after I get this one piece figured out) The first column is Month Cycle (Mid-Month, End Of Month) the second is Month (January thru December) and the third is Year (2022 to 2030). The columns are set with drop down boxes pulling the information from 3 different worksheets to minimize typing all the time. I am trying to get a fourth column to TextJoin these three columns in VBA. I have succeeded with one specified row, however, I would like it to TextJoin each new row of information automatically when it is entered, if it can't be done automatically then a button is also ok. I just need to break through this wall I have hit. 

 

My VBA Code:

 

Private Sub CommandButton1_Click()
Dim result As String

result = WorksheetFunction.TextJoin(" - ", True, Range("A3:E3"))

Worksheets("Sheet1").Range("G3").Value = result

Worksheets("Sheet1").Range("G3").Select

End Sub

 

Thank you in advance for any assistance you can give me.

excel
Macros and VBA

3 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 10, 2022

    TimothySmith 

    Private Sub CommandButton1_Click()

    Dim result As String
    Dim size As Integer
    Dim cell_entries() As String
    Dim i As Integer
    Dim k As Integer
    Dim j As Integer

    size = 4
    k = 1

    For j = 3 To 30

    ReDim cell_entries(size)

    For i = 0 To size

    cell_entries(i) = Cells(j, k).Value
    k = k + 1
    Next i

    result = Join(cell_entries)
    Worksheets("Sheet1").Cells(j, 7).Value = result

    k = 1

    Next j

    End Sub

     

    Maybe with this code.

    • TimothySmith's avatar
      TimothySmith
      Copper Contributor
      Jan 10, 2022
      Thank you Quadruple_Pawn for such a quick response. I appreciate what you and others do to help those of us who are not so savvy when it comes to programming. I will give it a try...

      Thank you again

Resources