Jay Petrulis or anyone else.

[Zac]

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

[Zac]

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

[Jay Petrulis]

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

[Zac]

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

[Zac]

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.

[Jay Petrulis]

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