rows
ehmmm.... it's me again....
my sheet contains 2 identical tables (it has to fit on an A4 paper). each table has 4 columns and 17 rows... the data from my userform goes first in row 1 (starts in B16) then in 2 etc... when row 17 is full I need the data to go to the next table (starts in P16) and down the next 17 rows. To fill the first 17 rows I use this (thanks Steve Bell): Private Sub cmdNext_Click() Sheets("Sheet1").Cells(1, 30).Value = ComboClient.Text Sheets("Sheet").Cells(9, 22).Value = UCase(TextData.Text) Dim lrw As Long lrw = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row If lrw < 16 Then lrw = 16 End If Sheets("Sheet1").Cells(lrw, 2) = TxtCode.Value Sheets("Sheet1").Cells(lrw, 4) = TxtBill.Value Sheets("Sheet1").Cells(lrw, 8) = TxtExNr.Value Sheets("Sheet1").Cells(lrw, 11) = TxtData.Value End Sub Any suggestions? |
rows
Hi,
Try this : Option Explicit Private Sub cmdNext_Click() Sheets("Sheet1").Cells(1, 30).Value = ComboClient.Text Sheets("Sheet").Cells(9, 22).Value = UCase(TextData.Text) Dim lrw As Long Dim col As Integer col = 2 ' Column B lrw = Sheets("Sheet1").Cells(Rows.Count, col).End(xlUp).Offset(1, 0).Row If lrw 32 Then ' Column B is full col = 16 ' Set to column P lrw = Sheets("Sheet1").Cells(Rows.Count, col).End(xlUp).Offset(1, 0).Row End If If lrw < 16 Then lrw = 16 End If Sheets("Sheet1").Cells(lrw, col) = TxtCode.Value Sheets("Sheet1").Cells(lrw, col + 2) = TxtBill.Value Sheets("Sheet1").Cells(lrw, col + 6) = TxtExNr.Value Sheets("Sheet1").Cells(lrw, col + 9) = TxtData.Value End Sub "antonov" wrote: ehmmm.... it's me again.... my sheet contains 2 identical tables (it has to fit on an A4 paper). each table has 4 columns and 17 rows... the data from my userform goes first in row 1 (starts in B16) then in 2 etc... when row 17 is full I need the data to go to the next table (starts in P16) and down the next 17 rows. To fill the first 17 rows I use this (thanks Steve Bell): Private Sub cmdNext_Click() Sheets("Sheet1").Cells(1, 30).Value = ComboClient.Text Sheets("Sheet").Cells(9, 22).Value = UCase(TextData.Text) Dim lrw As Long lrw = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row If lrw < 16 Then lrw = 16 End If Sheets("Sheet1").Cells(lrw, 2) = TxtCode.Value Sheets("Sheet1").Cells(lrw, 4) = TxtBill.Value Sheets("Sheet1").Cells(lrw, 8) = TxtExNr.Value Sheets("Sheet1").Cells(lrw, 11) = TxtData.Value End Sub Any suggestions? |
rows
yep... that's it... it works just fine... thanks
"Toppers" wrote in message ... Hi, Try this : Option Explicit Private Sub cmdNext_Click() Sheets("Sheet1").Cells(1, 30).Value = ComboClient.Text Sheets("Sheet").Cells(9, 22).Value = UCase(TextData.Text) Dim lrw As Long Dim col As Integer col = 2 ' Column B lrw = Sheets("Sheet1").Cells(Rows.Count, col).End(xlUp).Offset(1, 0).Row If lrw 32 Then ' Column B is full col = 16 ' Set to column P lrw = Sheets("Sheet1").Cells(Rows.Count, col).End(xlUp).Offset(1, 0).Row End If If lrw < 16 Then lrw = 16 End If Sheets("Sheet1").Cells(lrw, col) = TxtCode.Value Sheets("Sheet1").Cells(lrw, col + 2) = TxtBill.Value Sheets("Sheet1").Cells(lrw, col + 6) = TxtExNr.Value Sheets("Sheet1").Cells(lrw, col + 9) = TxtData.Value End Sub "antonov" wrote: ehmmm.... it's me again.... my sheet contains 2 identical tables (it has to fit on an A4 paper). each table has 4 columns and 17 rows... the data from my userform goes first in row 1 (starts in B16) then in 2 etc... when row 17 is full I need the data to go to the next table (starts in P16) and down the next 17 rows. To fill the first 17 rows I use this (thanks Steve Bell): Private Sub cmdNext_Click() Sheets("Sheet1").Cells(1, 30).Value = ComboClient.Text Sheets("Sheet").Cells(9, 22).Value = UCase(TextData.Text) Dim lrw As Long lrw = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row If lrw < 16 Then lrw = 16 End If Sheets("Sheet1").Cells(lrw, 2) = TxtCode.Value Sheets("Sheet1").Cells(lrw, 4) = TxtBill.Value Sheets("Sheet1").Cells(lrw, 8) = TxtExNr.Value Sheets("Sheet1").Cells(lrw, 11) = TxtData.Value End Sub Any suggestions? |
All times are GMT +1. The time now is 05:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com