Add multiple values from userform and table to single cell VBA

[KestutisTower]

Hello!

I have a code that transfers data from userform to datasheet.

Sub Submit()

   Dim sh As Worksheet
   Dim iRow As Long
   
   Set sh = ThisWorkbook.Sheets("Pardavimai")
   iRow = [Counta(Pardavimai!A:A)] + 1
   
   With sh
       
       .Cells(iRow, 1) = UserForm1.txtVardas.Value
       
       .Cells(iRow, 10) = UserForm1.txtPreke.Value & "   " & UserForm1.txtKiekis.Value & " vnt." & "   " & UserForm1.txtKomentaras.Value & " - "
       
       .Cells(iRow, 11) = [Text(Now(), "DD-MM-YYYY")]
       
       .Cells(iRow, 2) = UserForm1.txtPreke.Value
       
       .Cells(iRow, 3) = UserForm1.txtKiekis.Value
       
       .Cells(iRow, 4) = UserForm1.txtKomentaras.Value
       
       .Cells(iRow, 6) = UserForm1.txtVienetoKaina.Value
              
   End With
    
End Sub

it transfers data to table:

Book111.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Pirkėjas	Prekė	Kiekis	Komentaras	Bendra kaina	Vieneto kaina	LT kaina	Pelnas	Rita/Loreta	Pilnas	Data
2	kestas	akiniai	1	juodi	15	15				akiniai 1 vnt. juodi -	2022.06.10
3	rita	suknele	1	xl zalia	25	25				suknele 1 vnt. xl zalia -	2022.06.10
4	kestas	maike	1	xxl	5	5				maike 1 vnt. xxl -	2022.06.10
5	kestas	kremas	1	2	2	2				kremas 1 vnt. 2 -	2022.06.09
6	rita	lankelis	1	zalias	7	7				lankelis 1 vnt. zalias -	2022.06.10
7	rita	kremas	2	ranku	4	2				kremas 2 vnt. ranku -	2022.06.11
Pardavimai

Cell Formulas
Range	Formula
E2:E7	E2	=F2*C2

line 8 in, the code ads values to a cell values in column J. What I need is to Also add value of coresponding cell in column E. So cell value in J1 would show " akiniai 1 vnt. juodi - 15 ", J2 - " suknele 1 vnt. xl zalia - 25 " and so on.

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Sub Submit()

   Dim sh       As Worksheet
   Dim iRow     As Long
   
   Set sh = ThisWorkbook.Worksheets("Pardavimai")
   iRow = [Counta(Pardavimai!A:A)] + 1
   
   With sh
       
       .Cells(iRow, 1) = Me.txtVardas.Value
       
       .Cells(iRow, 2) = Me.txtPreke.Value
       
       .Cells(iRow, 3) = Me.txtKiekis.Value
       
       .Cells(iRow, 4) = Me.txtKomentaras.Value
       
       .Cells(iRow, 6) = Me.txtVienetoKaina.Value
       
       .Cells(iRow, 10) = Me.txtPreke.Value & "   " & _
                          Me.txtKiekis.Value & " vnt." & "   " & _
                          Me.txtKomentaras.Value & " - " & _
                          Val(Me.txtVienetoKaina) * Val(Me.txtKiekis.Value)
                          
      .Cells(iRow, 11) = [Text(Now(), "DD-MM-YYYY")]
              
   End With
    
End Sub

Note I have replaced the hard coded UserForm1 with the Me keyword as allows you to refer to the object without specifying the name. However, this change does assume that the code is in your Userforms code page.

Hope Helpful

Dave

 

[KestutisTower]

Hi,
untested but see if this update to your code does what you want

Sub Submit()

   Dim sh       As Worksheet
   Dim iRow     As Long
  
   Set sh = ThisWorkbook.Worksheets("Pardavimai")
   iRow = [Counta(Pardavimai!A:A)] + 1
  
   With sh
      
       .Cells(iRow, 1) = Me.txtVardas.Value
      
       .Cells(iRow, 2) = Me.txtPreke.Value
      
       .Cells(iRow, 3) = Me.txtKiekis.Value
      
       .Cells(iRow, 4) = Me.txtKomentaras.Value
      
       .Cells(iRow, 6) = Me.txtVienetoKaina.Value
      
       .Cells(iRow, 10) = Me.txtPreke.Value & "   " & _
                          Me.txtKiekis.Value & " vnt." & "   " & _
                          Me.txtKomentaras.Value & " - " & _
                          Val(Me.txtVienetoKaina) * Val(Me.txtKiekis.Value)
                         
      .Cells(iRow, 11) = [Text(Now(), "DD-MM-YYYY")]
             
   End With
   
End Sub

Note I have replaced the hard coded UserForm1 with the Me keyword as allows you to refer to the object without specifying the name. However, this change does assume that the code is in your Userforms code page.

Hope Helpful

Dave

Thanks a lot dmt32!
the code was in module, but You have suggested the way and I have done the job. Thank you again.

 

[dmt32]

Thanks a lot dmt32!
the code was in module, but You have suggested the way and I have done the job. Thank you again.

You are welcome - glad update does what you want

For interest, if want to keep your code in a standard module all need do is to pass the UserForm as an argument to your procedure

Modified code:

Sub Submit(ByVal Form As Object)

   Dim sh       As Worksheet
   Dim iRow     As Long
   
   Set sh = ThisWorkbook.Worksheets("Pardavimai")
   iRow = [Counta(Pardavimai!A:A)] + 1
   
   With sh
       
       .Cells(iRow, 1) = Form.txtVardas.Value
       
       .Cells(iRow, 2) = Form.txtPreke.Value
       
       .Cells(iRow, 3) = Form.txtKiekis.Value
       
       .Cells(iRow, 4) = Form.txtKomentaras.Value
       
       .Cells(iRow, 6) = Form.txtVienetoKaina.Value
       
       .Cells(iRow, 10) = Form.txtPreke.Value & "   " & _
                          Form.txtKiekis.Value & " vnt." & "   " & _
                          Form.txtKomentaras.Value & " - " & _
                          Val(Form.txtVienetoKaina) * Val(Form.txtKiekis.Value)
                          
      .Cells(iRow, 11) = [Text(Now(), "DD-MM-YYYY")]
              
   End With
    
End Sub

and to call it from your userform

 Submit Me

Dave

 

[KestutisTower]

Ok thanks .