rows

[antonov]

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?

 

[Guest]

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]

yep... that's it... it works just fine... thanks

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