Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kink in locating last row
I use a userform for a user to input item number and qty. With a
commandbutton click event, code is executed to locate the last row and then place the data into the cells. Because of the layout of the information to be printed, I am forced to create two sections within the spreadsheet. Let me explain better. FORM-1 FORM-2 A13:A50 & G13:G50 = item number B13:B50 & H13:H50 = qty C13:C50 & I13:I50 = Item description (pulled from another sheet w/ item number match) D13:D50 & J13:J50 = Misc Info about that item The forms, as you can see, are identical. Once form-1 is filled up, then I need the to start using form-2. Each form allows for 38 items to be ordered. The problem is that the code for locating the last row would not work since the first form is filled up to row 50 and then I start filling the second form at row 13 again. Below is the code that is used as I described. How do I go about bypassing the last row as being 50 (or 51 as last empty row) in order to be able to use the second form? Dim rng As Range With Worksheets("Order") Set rng = .Cells(Rows.Count, 1).End(xlUp)(2) If rng.Row < 13 Then Set rng = .Cells(13, 1) End If End With rng.Value = txtItemNum rng.Offset(0, 1).Value = txtQty Thanks for your help in advance! Les |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kink in locating last row
Dim rng As Range
Dim icol as Long With Worksheets("Order") if application.CountA(.Range("A13:A50")) < 38 then icol = 1 else icol = 7 End if Set rng = .Cells(Rows.Count, icol).End(xlUp)(2) If rng.Row < 13 Then Set rng = .Cells(13, icol) End If End With rng.Value = txtItemNum rng.Offset(0, 1).Value = txtQty -- Regards, Tom Ogilvy "WLMPilot" wrote in message ... I use a userform for a user to input item number and qty. With a commandbutton click event, code is executed to locate the last row and then place the data into the cells. Because of the layout of the information to be printed, I am forced to create two sections within the spreadsheet. Let me explain better. FORM-1 FORM-2 A13:A50 & G13:G50 = item number B13:B50 & H13:H50 = qty C13:C50 & I13:I50 = Item description (pulled from another sheet w/ item number match) D13:D50 & J13:J50 = Misc Info about that item The forms, as you can see, are identical. Once form-1 is filled up, then I need the to start using form-2. Each form allows for 38 items to be ordered. The problem is that the code for locating the last row would not work since the first form is filled up to row 50 and then I start filling the second form at row 13 again. Below is the code that is used as I described. How do I go about bypassing the last row as being 50 (or 51 as last empty row) in order to be able to use the second form? Dim rng As Range With Worksheets("Order") Set rng = .Cells(Rows.Count, 1).End(xlUp)(2) If rng.Row < 13 Then Set rng = .Cells(13, 1) End If End With rng.Value = txtItemNum rng.Offset(0, 1).Value = txtQty Thanks for your help in advance! Les |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kink in locating last row
Tom,
Thanks for all your help. It works great!! Les "Tom Ogilvy" wrote: Dim rng As Range Dim icol as Long With Worksheets("Order") if application.CountA(.Range("A13:A50")) < 38 then icol = 1 else icol = 7 End if Set rng = .Cells(Rows.Count, icol).End(xlUp)(2) If rng.Row < 13 Then Set rng = .Cells(13, icol) End If End With rng.Value = txtItemNum rng.Offset(0, 1).Value = txtQty -- Regards, Tom Ogilvy "WLMPilot" wrote in message ... I use a userform for a user to input item number and qty. With a commandbutton click event, code is executed to locate the last row and then place the data into the cells. Because of the layout of the information to be printed, I am forced to create two sections within the spreadsheet. Let me explain better. FORM-1 FORM-2 A13:A50 & G13:G50 = item number B13:B50 & H13:H50 = qty C13:C50 & I13:I50 = Item description (pulled from another sheet w/ item number match) D13:D50 & J13:J50 = Misc Info about that item The forms, as you can see, are identical. Once form-1 is filled up, then I need the to start using form-2. Each form allows for 38 items to be ordered. The problem is that the code for locating the last row would not work since the first form is filled up to row 50 and then I start filling the second form at row 13 again. Below is the code that is used as I described. How do I go about bypassing the last row as being 50 (or 51 as last empty row) in order to be able to use the second form? Dim rng As Range With Worksheets("Order") Set rng = .Cells(Rows.Count, 1).End(xlUp)(2) If rng.Row < 13 Then Set rng = .Cells(13, 1) End If End With rng.Value = txtItemNum rng.Offset(0, 1).Value = txtQty Thanks for your help in advance! Les |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating amesaage | Excel Discussion (Misc queries) | |||
Locating the max value AND performing a fn on it only | Excel Discussion (Misc queries) | |||
Locating | New Users to Excel | |||
locating the top 5 number (in a col) | Excel Worksheet Functions | |||
locating charts | Excel Programming |