Data Entry - Write to next cell

[Tony]

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]

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]

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]

Add a line to do the work...

..Cells(nextrow, 16).Value = OneThing
..Cells(nextrow + 1, 16).Value = OrAnother