Jay Petrulis or anyone else.

Zac

Well-known Member
Joined
Feb 20, 2002
Messages
796
My saga continues.

Still trying to clean up the mcaro I recorded.

In "COL D", I want to enter TEXT as "PAI",
then have it copied down to where the data ends in the adjacent Col "C".

How can I accomplish this?

Finally;
"R" lists my salesmans COMM Rate.
"O" lists the Sales for an item for the month.
From P2: where-ever the data ends in "O",
I need to start by taking R2*O2, and conyinue on down the line as far as the data entends in "O".

Can I do something like this:

Dim lastrow As Long

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

End Sub

Have a Great Day,
Zac
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Used the following and it worked:

Dim lastrow As Long

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

End Sub
 
Upvote 0
Nice job. Best way to learn is to figure it out for yourself.

If I understand correctly, you recorded a macro and deciphered what resulted. Then, you saw the limitations of that and received an answer which worked, but you did not understand how. A bit of tinkering, and you now have a pretty good grasp of what is going on. It will only get better, too!

Stick with the board and in a few months you will look back at your requests and say, "That was so easy..."

Bye,
Jay
 
Upvote 0
Jay,

What I've been doing is running (stepping into) the original recorded MACRO line by line to see what results. I get to the last part I'm trying to clean up, and that's formatting Col "P".

Then I stop just before the "crash" begins.
I create a macro on the side and attach it to a button using the code below. It runs fine. I then copied it into the original MACRO, and it fails. !?!?!?!?!?!?!?

The problem.
I inserted the following into the MACRO I initially recorded, and I'm getting a Debug Msg on the "lastrow As Long".

'This Code takes N2*M2, places it in O2, then copies the formula
'all the way to the bottom of the ACTICE SHEET.
Dim lastrow As Long
With ActiveSheet
.UsedRange
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
.Range("O2:O" & lastrow) = "=RC[-2]*RC[-1]"
'End Formating "O".
'The Following Code takes what is in (R2*.01) to the Bottom
'of the Active Sheet.
Application.Goto Reference:="R2C15"
Dim UsedRng As Range, UsedCell As Range
With ActiveSheet
Set UsedRng = Intersect(.UsedRange, .Range("R2:R65536"))
For Each UsedCell In UsedRng
UsedCell = UsedCell * 0.01
Next UsedCell
'End Formating "R"
'The following Formats Column "P" as required.
Dim lastrow As Long
With ActiveSheet
.UsedRange
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
.Range("P2:P" & lastrow) = "=RC[-1]*RC[+2]"
'End Formating "P".

It doesn't fail on the previous lastrow stmnt.

Any ideas?

Zac
 
Upvote 0
Jay,

I was getting a "Duplicate Stmnt" error so I
placed a ( ' ) in front of the Dim lastrow As Long stmnt to "text" it.

At the end of my MACRO I have

End With

End Sub

When I start to step into the macro line by line the first error msg I get states you have a compiling error, that is looking for an "End With" stmnt. Which I have.
So I "text" the End With, and the next error I get is that it's looking for an End Sub.
Which I had.

Now I'm stumped for sure.
 
Upvote 0
Hi,

Don't know for sure, but I suspect that you've already dimensioned the variable and need to use the ReDim statement to redimension it.

However, that is not needed in this case, as you already have found the last row on the worksheet. You can reuse it.

A few points to note.

1. Dim statements declare variable types and go at the top (for readability). We won't go into global variables here.

2. Once you are working with the activesheet, you don't need to redo the setup if you want to work with another range.

3. The 'lastrow' determination has been done twice in your code, but you haven't added or deleted any rows, so you gain nothing, as lastrow is the same at the top of the procedure and the bottom.

Try the following:

'-----------
Sub test()
Dim lastrow As Long
Dim UsedRng As Range, UsedCell As Range

With ActiveSheet
.UsedRange 'Resets the range (so I'm told)

lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
.Range("O2:O" & lastrow) = "=RC[-2]*RC[-1]"
.Range("P2:P" & lastrow) = "=RC[-1]*RC[+2]"

Set UsedRng = .Range("R2:R" & lastrow)
For Each UsedCell In UsedRng
UsedCell = UsedCell * 0.01
Next UsedCell

End With

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

I prefer to load all the formulas at runtime and at one shot. To me, this is much easier to read than what you have.

Bye,
Jay
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top