Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Control Data Entry - push entry to next cell Ofelia Excel Discussion (Misc queries) 0 July 7th 08 04:19 PM
can I write an Excel macro to pause for data entry then continue? kiwiwaldo Excel Discussion (Misc queries) 3 June 30th 08 10:25 AM
how do I write a formula to select the last entry in a colum Jme Excel Discussion (Misc queries) 1 June 12th 08 05:06 PM
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing [email protected] Excel Discussion (Misc queries) 0 March 20th 08 01:45 PM
Cell Entry That Locks Selected Cells From Any Data Entry. ron Excel Worksheet Functions 5 February 16th 07 10:52 PM


All times are GMT +1. The time now is 06:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"