Jay Petrulis or anyone else.

Zac · Apr 29th, 2002, 01:13 PM

Have a Unit Price Col in "N".
My recorded MACRO used an AutoFill scenario but is fails this time around.

What code can I add in "O" that takes N2*M2,
and copies it to the bottom of the ActiveSheet? My problem in the current macro is that the # of rows varies from month to month, and the AutoFill I used previously fails this time around.

For Jay Petrulis:
The previous code you recommended worked fine.

If you would like more info let me know.

Zac

Jay Petrulis · Apr 29th, 2002, 01:25 PM

Hi,

Pick one of the options. Both are one line. The first shows the resulting value of the multiplication. The second pastes the formula to the worksheet cell, so it will adjust if M2 or N2 are changed.

'----------------
Sub test()

Cells(Cells(Rows.Count, "O").End(xlUp).Row + 1, "O") _
= Range("M2") * Range("N2")

Cells(Cells(Rows.Count, "O").End(xlUp).Row + 1, "O") _
= "=R2C[-2]*R2C[-1]"

End Sub
'---------------

Bye,
Jay

EDIT: I think I misread your question.

Try,

Sub test()
Dim lastrow As Long

With ActiveSheet
.UsedRange
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
.Range("O2:O" & lastrow) = "=RC[-2]*RC[-1]"
End With

End Sub

[ This Message was edited by: Jay Petrulis on 2002-04-29 12:32 ]

Zac · Apr 29th, 2002, 01:33 PM

Zac · Apr 29th, 2002, 01:42 PM

Jay,

It worked great. Thank You Very Much.

IS there any "site" I can go to learn how to use or create code like this?

Jay Petrulis · Apr 29th, 2002, 01:53 PM

Hi,

Try MrExcel.com

Seriously, get one of John Walkenbach's programming books to start. Then, grab as much as you can from this board and the following:

http://communities.microsoft.com/new...cp=Prod_Office

You will see tremendous improvement just watching. Also, answer questions if you can. If you are wrong, somebody will point it out in a very nice way, as these Excel boards are the nicest around.

Also, there are numerous sites where you can learn a ton.

Chip Pearson: http://www.cpearson.com
David McRitchie: http://www.mvps.org/dmcritchie/excel/excel.htm
Dave Hawley: http://www.microsoftexceltraining.com

Many, many others to help you as well.

Bye,
Jay

Zac · Apr 29th, 2002, 02:03 PM

Jay,

Just picked up EXCEL 2000 Power Programming with VBA, by Walkenbach a month or so ago.
Haven't had an enormous amount of time to spend with it, but I did learn a little so far.

Most of what I learn has been self-taught,
and a lot I get from this forum.

All who have answered me have been great, and
I appreciate it very much.

BEst Regards,
Zac

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 01:13 PM	#1
Zac Board Regular Join Date: Feb 2002 Location: Mike Posts: 796	Have a Unit Price Col in "N". My recorded MACRO used an AutoFill scenario but is fails this time around. What code can I add in "O" that takes N2*M2, and copies it to the bottom of the ActiveSheet? My problem in the current macro is that the # of rows varies from month to month, and the AutoFill I used previously fails this time around. For Jay Petrulis: The previous code you recommended worked fine. If you would like more info let me know. Zac

Apr 29th, 2002, 01:25 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, Pick one of the options. Both are one line. The first shows the resulting value of the multiplication. The second pastes the formula to the worksheet cell, so it will adjust if M2 or N2 are changed. '---------------- Sub test() Cells(Cells(Rows.Count, "O").End(xlUp).Row + 1, "O") _ = Range("M2") * Range("N2") Cells(Cells(Rows.Count, "O").End(xlUp).Row + 1, "O") _ = "=R2C[-2]R2C[-1]" End Sub '--------------- Bye, Jay EDIT: I think I misread your question. Try, Sub test() Dim lastrow As Long With ActiveSheet .UsedRange lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row .Range("O2:O" & lastrow) = "=RC[-2]RC[-1]" End With End Sub [ This Message was edited by: Jay Petrulis on 2002-04-29 12:32 ]

Apr 29th, 2002, 01:33 PM	#3
Zac Board Regular Join Date: Feb 2002 Location: Mike Posts: 796

Apr 29th, 2002, 01:42 PM	#4
Zac Board Regular Join Date: Feb 2002 Location: Mike Posts: 796	Jay, It worked great. Thank You Very Much. IS there any "site" I can go to learn how to use or create code like this?

Apr 29th, 2002, 01:53 PM	#5
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, Try MrExcel.com Seriously, get one of John Walkenbach's programming books to start. Then, grab as much as you can from this board and the following: http://communities.microsoft.com/new...cp=Prod_Office You will see tremendous improvement just watching. Also, answer questions if you can. If you are wrong, somebody will point it out in a very nice way, as these Excel boards are the nicest around. Also, there are numerous sites where you can learn a ton. Chip Pearson: http://www.cpearson.com David McRitchie: http://www.mvps.org/dmcritchie/excel/excel.htm Dave Hawley: http://www.microsoftexceltraining.com Many, many others to help you as well. Bye, Jay

Apr 29th, 2002, 02:03 PM	#6
Zac Board Regular Join Date: Feb 2002 Location: Mike Posts: 796	Jay, Just picked up EXCEL 2000 Power Programming with VBA, by Walkenbach a month or so ago. Haven't had an enormous amount of time to spend with it, but I did learn a little so far. Most of what I learn has been self-taught, and a lot I get from this forum. All who have answered me have been great, and I appreciate it very much. BEst Regards, Zac