Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colating multi rows of data into single rows - no to pivot tables! | Excel Worksheet Functions | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions |