Write in two ways to two different worksheets

Mildpower · Apr 23rd 2020

All,

I hope you can help me with this.

I already have one part of the code ready, but i have to convert this part to also write in a different sheet and instead of horizontal it should be vertical

Code

Set wsAE = Worksheets("Sheet5")
If MsgBox("Entrada correcta?", vbYesNo + vbQuestion, "Verificar los datos!") = vbNo Then Exit Sub
iRow = wsAE.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
wsAE.Cells(iRow, 1).Resize(, 20).Value = Array(CMBCLIENT.Value, CMBPRESTADORA.Value, CMBDESPERSIONES.Value, CMBCONCEPTO.Value, CMBCOMISON.Value, cmbcosto.Value, CMBTIPO.Value, _
CMBEMPRESA.Value, CMBIVA.Value, TXTIMPORTE.Value, TXTIMPORTEBRUTO.Value, TXTFECHA.Value, TXTSUMCOM.Value, TXTSUBTOTAL.Value, TXTSUMIVA.Value, TXTSUMFACT.Value, TXTPAGOCLIENTE.Value, _
TXTREMANTE1.Value, TXTSUMCOSTO.Value, TXTREMANTE2.Value)

I need to write this in a worksheet called AOM and the first empty cell is E7

Can somebody kick me to the correct direction, cause it is driving me nuts

dangelor · Apr 23rd 2020

As a kick start, to write the array vertically...

Code

wsAE.Cells(iRow, 1).Resize(20).Value = Application.Transpose(Array(...)

Mildpower · Apr 23rd 2020

Quote from dangelor
As a kick start, to write the array vertically...
Code
wsAE.Cells(iRow, 1).Resize(20).Value = Application.Transpose(Array(...)

Hmm i am getting now a runtime error "1004"

dangelor · Apr 23rd 2020

What line is reporting the error?

Mildpower · Apr 23rd 2020

Heu everything.

Maybe i have to aproach this different;

How can i find and write into the next blank column, from a form in Excel.

With this i can find the last column

Code

lastcol = Cells(1, Columns.Count).End(xlToLeft).Column

But how can i address that:

txt.value goes to E7

txt2.value goes to E8

etc

And the next time it should go to

txt.value goes to F7

txt2.value goes to F8

etc

dangelor · Apr 23rd 2020

Possibly...

Code

Dim lCol As Long
With Worksheets("AOM")
lCol = .Cells(7, .Columns.Count).End(xlToLeft).Column + 1
wsAE.Cells(7, lCol).Resize(...
End With

Mildpower · Apr 24th 2020

So finaly i have it so far that i am writing in the correct order, but it does not goes to the new column.....

Code

Private Sub CMB_addnew_Click()
Dim lColumn As Long
Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("AOM")
TXTIMPORTE.Value = Val(TXTIMPORTEBRUTO.Value) / 1.16
TXTSUMCOM.Value = Val(TXTIMPORTE.Value) * Val(CMBCOMISON.Value)
TXTSUBTOTAL.Value = Val(TXTIMPORTE.Value) + Val(TXTSUMCOM.Value)
TXTSUMIVA.Value = Val(TXTSUBTOTAL.Value) * Val(CMBIVA.Value)
TXTSUMFACT.Value = Val(TXTSUBTOTAL.Value) + Val(TXTSUMIVA.Value)
TXTPAGOCLIENTE.Value = TXTIMPORTE.Value
TXTSUMCOM2.Value = Val(TXTSUMFACT.Value) * Val(CMBCOMISION2.Value)
TXTRETORNO.Value = Val(TXTSUMFACT.Value) - Val(TXTSUMCOM2.Value)
TXTREMANTE1.Value = Val(TXTSUMFACT.Value) - Val(TXTRETORNO.Value)
TXTSUMCOSTO.Value = Val(TXTSUMFACT.Value) * Val(CMBCOSTO.Value)
TXTREMANTE2.Value = Val(TXTREMANTE1.Value) - Val(TXTSUMCOSTO.Value)
TXTREMANTE3.Value = Val(TXTREMANTE2.Value) - Val(TXTCOSTOINTERNO.Value)
TXTSUMComisionista1.Value = Val(TXTREMANTE3.Value) * Val(TXTComisionista1.Value)
TXTSUMComisionista2.Value = Val(TXTREMANTE3.Value) * Val(TXTComisionista2.Value)
TXTSUMCOM3.Value = Val(TXTSUMFACT.Value) * Val(CMBCOMISION3.Value)
TXTSUMYT.Value = Val(TXTREMANTE3.Value) * Val(TXTYT.Value)
TXTSUMComisionista3.Value = Val(TXTSUMComisionista1.Value) - Val(TXTSUMYT.Value) * Val(TXTComisionista1.Value)
TXTSUMComisionista4.Value = Val(TXTSUMComisionista2.Value) - Val(TXTSUMYT.Value) * Val(TXTComisionista2.Value)
lColumn = ActiveSheet.Cells(7, Columns.Count).End(xlToLeft).Column
Sheet16.range("test").Cells(1).Value2 = TXTFECHA.Value
Sheet16.range("test").Cells(2).Value2 = TXTIMPORTE.Value
Sheet16.range("test").Cells(3).Value2 = TXTSUMCOM.Value
Sheet16.range("test").Cells(4).Value2 = TXTSUBTOTAL.Value
Sheet16.range("test").Cells(5).Value2 = TXTSUMIVA.Value
Sheet16.range("test").Cells(6).Value2 = TXTSUMFACT.Value
Sheet16.range("test").Cells(7).Value2 = TXTSUMCOM2.Value
Sheet16.range("test").Cells(8).Value2 = TXTRETORNO.Value
Sheet16.range("test").Cells(9).Value2 = TXTREMANTE1.Value
Sheet16.range("test").Cells(10).Value2 = TXTSUMCOSTO.Value
Sheet16.range("test").Cells(11).Value2 = TXTREMANTE2.Value
Sheet16.range("test").Cells(12).Value2 = TXTCOSTOINTERNO.Value
Sheet16.range("test").Cells(13).Value2 = TXTREMANTE3.Value
Sheet16.range("test").Cells(15).Value2 = TXTSUMComisionista1.Value
Sheet16.range("test").Cells(16).Value2 = TXTSUMComisionista2.Value
Sheet16.range("test").Cells(17).Value2 = TXTSUMCOM3.Value
Sheet16.range("test").Cells(18).Value2 = TXTSUMYT.Value
Sheet16.range("test").Cells(19).Value2 = TXTSUMComisionista3.Value
Sheet16.range("test").Cells(20).Value2 = TXTSUMComisionista4.Value
'Columns.AutoFit
MsgBox "La nueva entrada ha sido guardada.", vbInformation, "Listo"
'Unload form
TXTSUMComisionista1.Value = ""
TXTSUMComisionista2.Value = ""
TXTSUMCOM3.Value = ""
TXTYT.Value = ""
TXTSUMYT.Value = ""
TXTSUMComisionista3.Value = ""
TXTSUMComisionista4.Value = ""
TXTREMANTE1.Value = ""
TXTSUMCOSTO.Value = ""
TXTCOSTOINTERNO.Value = ""
TXTREMANTE2.Value = ""
TXTSUMCOM2.Value = ""
TXTRETORNO.Value = ""
TXTSUMCOSTO.Value = ""
TXTREMANTE3.Value = ""
CMBIVA.Value = ""
TXTIMPORTEBRUTO.Value = ""
TXTIMPORTE.Value = ""
TXTSUMCOM.Value = ""
TXTSUBTOTAL.Value = ""
TXTSUMIVA.Value = ""
TXTSUMFACT.Value = ""
TXTPAGOCLIENTE.Value = ""
TXTFECHA.Value = ""
CMBCLIENT.Value = ""
CMBPRESTADORA.Value = ""
CMBDESPERSIONES.Value = ""
CMBCONCEPTO.Value = ""
CMBTIPO.Value = ""
CMBEMPRESA.Value = ""
CMBCOMISON.Value = ""
CMBCOSTO.Value = ""
End Sub

Display More

Mildpower · Apr 24th 2020

dangelor Thank you for the support, you pointed me to the correct direction!

I mannaged to fix it

Code

Private Sub casa()
Dim lColumn As Long
Set sh = Worksheets("Tu Casa Express")
lColumn = sh.Cells(6, Columns.Count).End(xlToLeft).Column + 1
Debug.Print lColumn
sh.Cells(lColumn)(3).Value2 = CMBCLIENT.Value
sh.Cells(lColumn)(4).Value2 = CMBPRESTADORA.Value
sh.Cells(lColumn)(5).Value2 = CMBDESPERSIONES.Value
sh.Cells(lColumn)(6).Value2 = CMBCONCEPTO.Value
sh.Cells(lColumn)(7).Value2 = CMBTIPO.Value
sh.Cells(lColumn)(8).Value2 = CMBEMPRESA.Value
sh.Cells(lColumn)(9).Value2 = TXTFECHA.Value
sh.Cells(lColumn)(10).Value2 = TXTIMPORTE.Value
sh.Cells(lColumn)(11).Value2 = TXTSUMCOM.Value
sh.Cells(lColumn)(12).Value2 = TXTSUBTOTAL.Value
sh.Cells(lColumn)(13).Value2 = TXTSUMIVA.Value
sh.Cells(lColumn)(14).Value2 = TXTSUMFACT.Value
sh.Cells(lColumn)(15).Value2 = TXTSUMCOM2.Value
sh.Cells(lColumn)(16).Value2 = TXTRETORNO.Value
sh.Cells(lColumn)(17).Value2 = TXTREMANTE1.Value
sh.Cells(lColumn)(18).Value2 = TXTSUMCOSTO.Value
sh.Cells(lColumn)(19).Value2 = TXTREMANTE2.Value
sh.Cells(lColumn)(20).Value2 = TXTCOSTOINTERNO.Value
sh.Cells(lColumn)(21).Value2 = TXTREMANTE3.Value
sh.Cells(lColumn)(22).Value2 = TXTSUMComisionista1.Value
sh.Cells(lColumn)(23).Value2 = TXTSUMComisionista2.Value
sh.Cells(lColumn)(24).Value2 = TXTSUMCOM3.Value
sh.Cells(lColumn)(25).Value2 = TXTSUMYT.Value
sh.Cells(lColumn)(26).Value2 = TXTSUMComisionista3.Value
sh.Cells(lColumn)(27).Value2 = TXTSUMComisionista4.Value
End Sub

Display More

So this one is solved!

dangelor · Apr 24th 2020

Glad I was some help!

Mildpower · Apr 24th 2020

How the peep i close this

Write in two ways to two different worksheets

Participate now!

Share

Tags