Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance cell reference
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance cell reference
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. -- Regards, Tom Ogilvy "WLMPilot" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance cell reference
Thanks!! Worked great!!!! Appreciate your help.
Les "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "WLMPilot" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance cell reference
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "WLMPilot" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance cell reference
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. WLMPilot wrote: 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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "WLMPilot" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do not advance to next cell | Excel Worksheet Functions | |||
Finding Advance Filter Reference in Source Sheet | Excel Discussion (Misc queries) | |||
automatically advance to next cell | Excel Discussion (Misc queries) | |||
Cell advance i.e. tab | Excel Discussion (Misc queries) | |||
Advance to predetermined cell | Excel Worksheet Functions |