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

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

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