Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry - Write to next cell
I am trying to write a macro to write a order to a specific row. I can get
the first part of the entry to go into cell A12, the rest of the data will not transfer to the remaining cells (B12 to AG12). Also I want to enter a new record after this one on the next row and continue until the last eligible row has been entered (Row A32) and then stop. Note, Cell O12 & O13 are separate cells that have separate enteries. All other cells, A12&A13 are merged as one cell. I have the following code already that I am using: Private Sub NewRecord_Click() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Order Form") Dim nextrow As Integer 'find first empty row in database nextrow = Worksheets("Order Form").Range("a65536").End(xlUp).Row + 1 Worksheets("Order Form").Cells(nextrow, 1).Value = Quantity Worksheets("Order Form").Cells(9, 13).Value = OrderDate (doesn't work) 'Worksheets("Order Form").Cells(9, 2).Value = OrderDate (doesn't work) Can anyone help out with this? A1 - blank B1 C1 A2 - blank B2 C2 A3 - blank B3 C3 A4 - Header 1 B4 C4 A5 - Header 2 B5 C5 A6 - Header 3 B6 C6 A7 - Header 4 B7 C7 A8 - Header 5 B8 C8 A9 - blank B9 - Header 6 C9 - Header 6 A10 - blank B10 - Header 7 C10 - Header 7 A11 - Header 8 B11 - Header 8 C11 - Header 8 A12 - Data Line 1 B12 - Data Line 1 C12 - Data Line 1A A13 - Data Line 1 B13 - Data Line 1 C13 - Data Line 1B A14 - Data Line 1 B14 - Data Line 1 C14 - Data Line 1A A15 - Data Line 1 B15 - Data Line 1 C15 - Data Line 1B ~ ~ ~ A32 - Data Line 1 B32 - Data Line 1 C32 - Data Line 1A A33 - Data Line 1 B33 - Data Line 1 C33 - Data Line 1B A34 - blank B34 - blank C34 - blank ~ ~ ~ A47 - blank B47 - blank C47 - blank |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry - Write to next cell
Maybe...
Private Sub NewRecord_Click() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Order Form") Dim nextrow As Long 'find first empty row in database with worksheets("Order Form") nextrow = .Range("a65536").End(xlUp).Row + 1 end with 'always start with the even numbered row if nextrow mod 2 = 1 then nextrow = nextrow + 1 end if 'check to see if there's room if nextrow = 32 then msgbox "out of room!" exit sub end if with worksheets("Order form") .Cells(nextrow, 1).Value = Quantity .Cells(nextrow, 13).Value = OrderDate end with ..... Untested, uncompiled. Tony wrote: I am trying to write a macro to write a order to a specific row. I can get the first part of the entry to go into cell A12, the rest of the data will not transfer to the remaining cells (B12 to AG12). Also I want to enter a new record after this one on the next row and continue until the last eligible row has been entered (Row A32) and then stop. Note, Cell O12 & O13 are separate cells that have separate enteries. All other cells, A12&A13 are merged as one cell. I have the following code already that I am using: Private Sub NewRecord_Click() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Order Form") Dim nextrow As Integer 'find first empty row in database nextrow = Worksheets("Order Form").Range("a65536").End(xlUp).Row + 1 Worksheets("Order Form").Cells(nextrow, 1).Value = Quantity Worksheets("Order Form").Cells(9, 13).Value = OrderDate (doesn't work) 'Worksheets("Order Form").Cells(9, 2).Value = OrderDate (doesn't work) Can anyone help out with this? A1 - blank B1 C1 A2 - blank B2 C2 A3 - blank B3 C3 A4 - Header 1 B4 C4 A5 - Header 2 B5 C5 A6 - Header 3 B6 C6 A7 - Header 4 B7 C7 A8 - Header 5 B8 C8 A9 - blank B9 - Header 6 C9 - Header 6 A10 - blank B10 - Header 7 C10 - Header 7 A11 - Header 8 B11 - Header 8 C11 - Header 8 A12 - Data Line 1 B12 - Data Line 1 C12 - Data Line 1A A13 - Data Line 1 B13 - Data Line 1 C13 - Data Line 1B A14 - Data Line 1 B14 - Data Line 1 C14 - Data Line 1A A15 - Data Line 1 B15 - Data Line 1 C15 - Data Line 1B ~ ~ ~ A32 - Data Line 1 B32 - Data Line 1 C32 - Data Line 1A A33 - Data Line 1 B33 - Data Line 1 C33 - Data Line 1B A34 - blank B34 - blank C34 - blank ~ ~ ~ A47 - blank B47 - blank C47 - blank -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry - Write to next cell
Dave, the code worked great. I do have one minor change I need to make
though. In cell P12 instead of having a merged cell (P12&P13) I have two single cells. Each one of these cells needs to have a separate value. I can put the top value (P12) in okay, but I need some code to put a different value into cell P13. My next row of data will be rows 14&15 merged into one row up to column "P" again. Thanks, Tony "Dave Peterson" wrote: Maybe... Private Sub NewRecord_Click() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Order Form") Dim nextrow As Long 'find first empty row in database with worksheets("Order Form") nextrow = .Range("a65536").End(xlUp).Row + 1 end with 'always start with the even numbered row if nextrow mod 2 = 1 then nextrow = nextrow + 1 end if 'check to see if there's room if nextrow = 32 then msgbox "out of room!" exit sub end if with worksheets("Order form") .Cells(nextrow, 1).Value = Quantity .Cells(nextrow, 13).Value = OrderDate end with ..... Untested, uncompiled. Tony wrote: I am trying to write a macro to write a order to a specific row. I can get the first part of the entry to go into cell A12, the rest of the data will not transfer to the remaining cells (B12 to AG12). Also I want to enter a new record after this one on the next row and continue until the last eligible row has been entered (Row A32) and then stop. Note, Cell O12 & O13 are separate cells that have separate enteries. All other cells, A12&A13 are merged as one cell. I have the following code already that I am using: Private Sub NewRecord_Click() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Order Form") Dim nextrow As Integer 'find first empty row in database nextrow = Worksheets("Order Form").Range("a65536").End(xlUp).Row + 1 Worksheets("Order Form").Cells(nextrow, 1).Value = Quantity Worksheets("Order Form").Cells(9, 13).Value = OrderDate (doesn't work) 'Worksheets("Order Form").Cells(9, 2).Value = OrderDate (doesn't work) Can anyone help out with this? A1 - blank B1 C1 A2 - blank B2 C2 A3 - blank B3 C3 A4 - Header 1 B4 C4 A5 - Header 2 B5 C5 A6 - Header 3 B6 C6 A7 - Header 4 B7 C7 A8 - Header 5 B8 C8 A9 - blank B9 - Header 6 C9 - Header 6 A10 - blank B10 - Header 7 C10 - Header 7 A11 - Header 8 B11 - Header 8 C11 - Header 8 A12 - Data Line 1 B12 - Data Line 1 C12 - Data Line 1A A13 - Data Line 1 B13 - Data Line 1 C13 - Data Line 1B A14 - Data Line 1 B14 - Data Line 1 C14 - Data Line 1A A15 - Data Line 1 B15 - Data Line 1 C15 - Data Line 1B ~ ~ ~ A32 - Data Line 1 B32 - Data Line 1 C32 - Data Line 1A A33 - Data Line 1 B33 - Data Line 1 C33 - Data Line 1B A34 - blank B34 - blank C34 - blank ~ ~ ~ A47 - blank B47 - blank C47 - blank -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry - Write to next cell
Add a line to do the work...
..Cells(nextrow, 16).Value = OneThing ..Cells(nextrow + 1, 16).Value = OrAnother Tony wrote: Dave, the code worked great. I do have one minor change I need to make though. In cell P12 instead of having a merged cell (P12&P13) I have two single cells. Each one of these cells needs to have a separate value. I can put the top value (P12) in okay, but I need some code to put a different value into cell P13. My next row of data will be rows 14&15 merged into one row up to column "P" again. Thanks, Tony "Dave Peterson" wrote: Maybe... Private Sub NewRecord_Click() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Order Form") Dim nextrow As Long 'find first empty row in database with worksheets("Order Form") nextrow = .Range("a65536").End(xlUp).Row + 1 end with 'always start with the even numbered row if nextrow mod 2 = 1 then nextrow = nextrow + 1 end if 'check to see if there's room if nextrow = 32 then msgbox "out of room!" exit sub end if with worksheets("Order form") .Cells(nextrow, 1).Value = Quantity .Cells(nextrow, 13).Value = OrderDate end with ..... Untested, uncompiled. Tony wrote: I am trying to write a macro to write a order to a specific row. I can get the first part of the entry to go into cell A12, the rest of the data will not transfer to the remaining cells (B12 to AG12). Also I want to enter a new record after this one on the next row and continue until the last eligible row has been entered (Row A32) and then stop. Note, Cell O12 & O13 are separate cells that have separate enteries. All other cells, A12&A13 are merged as one cell. I have the following code already that I am using: Private Sub NewRecord_Click() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Order Form") Dim nextrow As Integer 'find first empty row in database nextrow = Worksheets("Order Form").Range("a65536").End(xlUp).Row + 1 Worksheets("Order Form").Cells(nextrow, 1).Value = Quantity Worksheets("Order Form").Cells(9, 13).Value = OrderDate (doesn't work) 'Worksheets("Order Form").Cells(9, 2).Value = OrderDate (doesn't work) Can anyone help out with this? A1 - blank B1 C1 A2 - blank B2 C2 A3 - blank B3 C3 A4 - Header 1 B4 C4 A5 - Header 2 B5 C5 A6 - Header 3 B6 C6 A7 - Header 4 B7 C7 A8 - Header 5 B8 C8 A9 - blank B9 - Header 6 C9 - Header 6 A10 - blank B10 - Header 7 C10 - Header 7 A11 - Header 8 B11 - Header 8 C11 - Header 8 A12 - Data Line 1 B12 - Data Line 1 C12 - Data Line 1A A13 - Data Line 1 B13 - Data Line 1 C13 - Data Line 1B A14 - Data Line 1 B14 - Data Line 1 C14 - Data Line 1A A15 - Data Line 1 B15 - Data Line 1 C15 - Data Line 1B ~ ~ ~ A32 - Data Line 1 B32 - Data Line 1 C32 - Data Line 1A A33 - Data Line 1 B33 - Data Line 1 C33 - Data Line 1B A34 - blank B34 - blank C34 - blank ~ ~ ~ A47 - blank B47 - blank C47 - blank -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control Data Entry - push entry to next cell | Excel Discussion (Misc queries) | |||
can I write an Excel macro to pause for data entry then continue? | Excel Discussion (Misc queries) | |||
how do I write a formula to select the last entry in a colum | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions |