userform VB problem

[Mattlake]

Hi

I am so sorry to ask this.

I have created (with a lot of help from this forum) a spreadsheet with a userform. I have come across an issue that I have a formula in column I (date column) that is down to line 400.

when i fill the form in it goes to line 401.

I am assuming that I would need to put in a VB command to say when i press add it pastes the fields and then paste the formula in after. is this possible? if it is any ideas on what this can be?

the below is the script that i seem to get working

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("2023")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws
.Cells(iRow, 1).Value = Me.ComboBox2.Value
.Cells(iRow, 2).Value = Me.ComboBox1.Value
.Cells(iRow, 3).Value = Me.TextBox1.Value
.Cells(iRow, 4).Value = Me.ComboBox3.Value
.Cells(iRow, 5).Value = Me.TextBox2.Value
.Cells(iRow, 6).Value = Me.ComboBox4.Value
.Cells(iRow, 7).Value = Me.ComboBox5.Value
.Cells(iRow, 8).Value = Me.ComboBox6.Value
.Cells(iRow, 10).Value = Me.ComboBox7.Value
.Cells(iRow, 11).Value = Me.ComboBox8.Value
.Cells(iRow, 12).Value = Me.TextBox3.Value
.Cells(iRow, 13).Value = Me.TextBox4.Value
.Cells(iRow, 14).Value = Me.TextBox5.Value
.Cells(iRow, 15).Value = Me.ComboBox9.Value
.Cells(iRow, 16).Value = Me.ComboBox11.Value
.Cells(iRow, 17).Value = Me.ComboBox12.Value
.Cells(iRow, 18).Value = Me.ComboBox10.Value
.Cells(iRow, 20).Value = Me.ComboBox13.Value
.Cells(iRow, 9).Value = concatenate(F2, " ", G2, " ", H2)
.Cells(iRow, 19).Value = concatenate(P2, " ", q2, " ", R2)



End With
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox6.Value = ""
Me.ComboBox7.Value = ""
Me.ComboBox8.Value = ""
Me.ComboBox9.Value = ""
Me.ComboBox10.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox12.Value = ""
Me.ComboBox13.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""


End Sub

I think i have put the concatenate in wrong.

any help would be appreciated

 

[DanteAmor]

Try this:

    .Cells(iRow, 9).Value = .Range("F2") & " " & .Range("G2") & " " & .Range("H2")
    .Cells(iRow, 19).Value = .Range("P2") & " " & .Range("Q2") & " " & .Range("R2")

 

[davesexcel]

Entering the formula or entering the value

.Cells(iRow, 9).Value = "=CONCATENATE(F2,"" "",G2,"" "",H2)"
.Cells(iRow, 9).Value= WorksheetFunction.TextJoin(" ", True, Range("F2:H2"))

 

[Mattlake]

thank you i thought i was trying too hard.

this one seems to work

    .Cells(iRow, 9).Value = [F2] & " " & [G2] & " " & [H2]
    .Cells(iRow, 19).Value = [P2] & " " & [Q2] & " " & [R2]

only question do i replace the F2 G2 etc with irow,6 and irow,7 etc? to get it to copy downwards?

 

[DanteAmor]

only question do i replace the F2 G2 etc with irow,6 and irow,7 etc? to get it to copy downwards?

In that case, if the result depends on the data in the row (iRow):

    .Cells(iRow, 9).Value = .Range("F" & iRow) & " " & .Range("G" & iRow) & " " & .Range("H" & iRow)
    .Cells(iRow, 19).Value = .Range("P" & iRow) & " " & .Range("Q" & iRow) & " " & .Range("R" & iRow)

 

[Mattlake]

it is always so simple when others show you the way

Thank you for your help with this