Advance cell reference

[Guest]

I am trying to use a userform for order entry (item number & qty). I am at
the point that I can enter only one item and place it in the appropriate
cells, but I need to be able to advance the cell reference for each entry.

The userform uses two command buttons: ENTER and FINISHED. Currently I am
using an click event on ENTER to process the info entered by user. Prior to
showing the userform, I have selected A13. Column A (starting with A13) is
ItemNum and Column B (starting with B13) is the qty.

I appreciate any suggestions on how to accomplish this task.

Thank you in advance,
Les

 

[Guest]

Private Sub CmdEnter_click()
Dim rng as Range
with worksheets("Data")
set rng = .cells(rows.count,1).End(xlup)(2)
if rng.row < 13 then
set rng = .Cells(13,1)
end with
rng.Value = TextBoxItemNum.Text
rng.offset(0,1).Value = TextBoxQty.Text
end Sub

obviously, the names used are illustrative. Replace with the actual names.

 

[Guest]

Thanks!! Worked great!!!! Appreciate your help.

Les

 

[Guest]

Tom

In general, I understand what this code that you sent me does, but I would
like to understand it better. I extracted the part I want to understand
better (as it appears in my macro). Specifically, can you tell me what the
second line is actually doing (noted with **)?

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

Thanks,
Les

 

[Dave Peterson]

I'm not Tom, but maybe...

First, the With statement above that line means that any object that starts with
a dot (.) will refer to the object in that with statement--in your case
worksheet("Order").

Set rng = .Cells(Rows.Count, 1).End(xlUp)(2)

..cells(rows.count,1)
is the same as the last cell in column 1 (aka A) (A65536 in xl2003).
..end(xlup) is like hitting the End key followed by the UpArrow (manually)
It goes up to the last used cell in column A.

(1) means to stay in the same location.
(2) means to come down one row (the next available row in that column).

I find this syntax easier to explain:
Set rng = .Cells(.Rows.Count, 1).End(xlUp).offset(1,0)

..offset(1,0) means to come down one row, but stay in the same column.

Chip has some notes written by Alan Beban:
http://www.cpearson.com/excel/cells.htm

It may be an interesting read for you.