Multiple Row

[Cyril Beki]

Hello, i plan to make a macro where the data will be place in multiple row after click the submit button.

below is the current situation:

Private Sub CommandButton1_Click()
Dim Pocl3 As Object
Set Pocl3 = Sheet2.Range("A99999").End(xlUp)

Pocl3.Offset(1, 1).Value = Me.TextBox1.Value & vbNewLine & Me.TextBox2.Value & vbNewLine & Me.TextBox3.Value & vbNewLine & Me.TextBox4.Value & vbNewLine & Me.TextBox6.Value
End Sub

Below is the one i want to achieve:

need help. Thank you in advance

 

[dmt32]

Hi,
untested but one way maybe

Option Base 1
Private Sub CommandButton1_Click()
    Dim Pocl3   As Object
    Dim arr     As Variant
    
    Set Pocl3 = Sheet2.Range("A99999").End(xlUp)
    
    arr = Array(Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox6.Value)
    
    Pocl3.Offset(1, 1).Resize(UBound(arr)).Value = Application.Transpose(arr)

End Sub

Dave

 

[Cyril Beki]

Hi,
untested but one way maybe

Option Base 1
Private Sub CommandButton1_Click()
    Dim Pocl3   As Object
    Dim arr     As Variant
   
    Set Pocl3 = Sheet2.Range("A99999").End(xlUp)
   
    arr = Array(Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox6.Value)
   
    Pocl3.Offset(1, 1).Resize(UBound(arr)).Value = Application.Transpose(arr)

End Sub

Dave

it work's but when i click the submit button again, the data not fill up the next row. I want it to be able to fill up the next empty row every time i clicked the submit button

 

[dmt32]

I want it to be able to fill up the next empty row every time i clicked the submit button

that may be because you are setting the Last Cell in Column A but posting data to Column B

try this update to your code

Option Base 1
Private Sub CommandButton1_Click()
    Dim Pocl3   As Range
    Dim arr     As Variant

    With Sheet2
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row, 1)
    End With
    
    arr = Array(Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox6.Value)
    
    Pocl3.Offset(1, 1).Resize(UBound(arr)).Value = Application.Transpose(arr)

End Sub

Dave

 

[Cyril Beki]

that may be because you are setting the Last Cell in Column A but posting data to Column B

try this update to your code

Option Base 1
Private Sub CommandButton1_Click()
    Dim Pocl3   As Range
    Dim arr     As Variant

    With Sheet2
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row, 1)
    End With
  
    arr = Array(Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox6.Value)
  
    Pocl3.Offset(1, 1).Resize(UBound(arr)).Value = Application.Transpose(arr)

End Sub

Dave

Now it's worked as requested.
But for TextBox93 representing Date, it's only show in one row (see below picture):

i wanted it to be filled as below when submit button is clicked:

Option Base 1
Private Sub CommandButton1_Click()
    Dim Pocl3   As Range
    Dim arr     As Variant
    Dim are     As Variant
    Dim ara     As Variant
    Dim arb     As Variant
    Dim arc     As Variant
    Dim ard     As Variant
    Dim arf     As Variant
    Dim arg     As Variant
    Dim arh     As Variant
    Dim ari     As Variant
    Dim arj     As Variant
    Dim ark     As Variant
    Dim arl     As Variant
    Dim arm     As Variant
    Dim arn     As Variant
    Dim aro     As Variant

    With Sheet2
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "C").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "D").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "E").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "F").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "G").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "H").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "I").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "J").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "K").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "L").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "M").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "N").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "O").End(xlUp).Row, 1)
        Set Pocl3 = .Cells(.Cells(.Rows.Count, "P").End(xlUp).Row, 1)
       
    End With
   
    arr = Array(Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox6.Value)
    are = Array(Me.TextBox7.Value, Me.TextBox8.Value, Me.TextBox9.Value, Me.TextBox10.Value, Me.TextBox11.Value)
    ara = Array(Me.TextBox12.Value, Me.TextBox13.Value, Me.TextBox14.Value, Me.TextBox15.Value, Me.TextBox16.Value)
    arb = Array(Me.TextBox17.Value, Me.TextBox18.Value, Me.TextBox19.Value, Me.TextBox20.Value, Me.TextBox21.Value)
    arc = Array(Me.TextBox22.Value, Me.TextBox23.Value, Me.TextBox24.Value, Me.TextBox25.Value, Me.TextBox26.Value)
    ard = Array(Me.TextBox27.Value, Me.TextBox28.Value, Me.TextBox29.Value, Me.TextBox30.Value, Me.TextBox31.Value)
    arf = Array(Me.TextBox32.Value, Me.TextBox33.Value, Me.TextBox34.Value, Me.TextBox35.Value, Me.TextBox36.Value)
    arg = Array(Me.TextBox37.Value, Me.TextBox38.Value, Me.TextBox39.Value, Me.TextBox40.Value, Me.TextBox41.Value)
    arh = Array(Me.TextBox42.Value, Me.TextBox43.Value, Me.TextBox44.Value, Me.TextBox45.Value, Me.TextBox46.Value)
    ari = Array(Me.TextBox52.Value, Me.TextBox53.Value, Me.TextBox54.Value, Me.TextBox55.Value, Me.TextBox56.Value)
    arj = Array(Me.TextBox57.Value, Me.TextBox58.Value, Me.TextBox59.Value, Me.TextBox60.Value, Me.TextBox61.Value)
    ark = Array(Me.TextBox62.Value, Me.TextBox63.Value, Me.TextBox64.Value, Me.TextBox65.Value, Me.TextBox66.Value)
    arl = Array(Me.TextBox67.Value, Me.TextBox68.Value, Me.TextBox69.Value, Me.TextBox70.Value, Me.TextBox71.Value)
    arm = Array(Me.TextBox72.Value, Me.TextBox73.Value, Me.TextBox74.Value, Me.TextBox75.Value, Me.TextBox76.Value)
    arn = Array(Me.TextBox77.Value, Me.TextBox78.Value, Me.TextBox79.Value, Me.TextBox80.Value, Me.TextBox81.Value)
    aro = Array(Me.TextBox93.Value)
   
    Pocl3.Offset(1, 1).Resize(UBound(arr)).Value = Application.Transpose(arr)
    Pocl3.Offset(1, 2).Resize(UBound(are)).Value = Application.Transpose(are)
    Pocl3.Offset(1, 3).Resize(UBound(ara)).Value = Application.Transpose(ara)
    Pocl3.Offset(1, 4).Resize(UBound(arb)).Value = Application.Transpose(arb)
    Pocl3.Offset(1, 5).Resize(UBound(arc)).Value = Application.Transpose(arc)
    Pocl3.Offset(1, 6).Resize(UBound(ard)).Value = Application.Transpose(ard)
    Pocl3.Offset(1, 7).Resize(UBound(arf)).Value = Application.Transpose(arf)
    Pocl3.Offset(1, 8).Resize(UBound(arg)).Value = Application.Transpose(arg)
    Pocl3.Offset(1, 9).Resize(UBound(arh)).Value = Application.Transpose(arh)
    Pocl3.Offset(1, 10).Resize(UBound(ari)).Value = Application.Transpose(ari)
    Pocl3.Offset(1, 11).Resize(UBound(arj)).Value = Application.Transpose(arj)
    Pocl3.Offset(1, 12).Resize(UBound(ark)).Value = Application.Transpose(ark)
    Pocl3.Offset(1, 13).Resize(UBound(arl)).Value = Application.Transpose(arl)
    Pocl3.Offset(1, 14).Resize(UBound(arm)).Value = Application.Transpose(arm)
    Pocl3.Offset(1, 15).Resize(UBound(arn)).Value = Application.Transpose(arn)
    Pocl3.Offset(1, 0).Resize(UBound(aro)).Value = Application.Transpose(aro)
   
End Sub

 

[dmt32]

try replacing this line

Pocl3.Offset(1, 0).Resize(UBound(aro)).Value = Application.Transpose(aro)

with this

Pocl3.Offset(1, 0).Resize(5).Value = Application.Transpose(aro)

and see if resolves issue for you

Dave