# Solved: Excel Macro, I'm Stuck!!!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### JUGGERNAUT21

This is just the basics of what I have. For my Macro I am really just recording my actions. I have an equation which I currently copy down. Problem is when I get this report it does not usually end at O1900. It varies every time. Is there an entry I can change to the code which will allow me to range the Final Commission down to the very last cell in column O???? Thanks!!

Company Cost Comm Trade Final Commission
ABC COMPANY \$1,500.00 .0100 \$.00 \$ 15.00
ZDYSD COMPANY \$9,301.00 .0200 \$.00 \$ 186.02
12312 COMPANY \$18,700.00.0300 \$.00 \$ 561.00
ADVANCED COMPANY \$2,321.43 .0400 -\$124.47 \$ 87.88

Columns("O:O").ColumnWidth = 9.14
Range("O2").Select
ActiveCell.FormulaR1C1 = "=(RC[-6]+RC[-1])*RC[-5]"
Range("O2").Select
Selection.Style = "Currency"
Selection.AutoFill Destination:=Range("O2:O1900"), Type:=xlFillDefault
Range("O2:O1900").Select
LastNumber = Range("b1").End(xlDown)
Range("b1").Select

#### Zack Barresse

Hi there,

Couldn't you just use something like this ...

Code:
``````Option Explicit

Sub JUGGERNtest()
With Range("O2", Cells(Rows.Count, "O").End(xlUp))
.Formula = "=(I2+N2)*J2"
.Style = "Currency"
.EntireColumn.AutoFit
End With
End Sub``````
??

#### bomb #21

Hi Zack -- that might not work. JUGGERNAUT21 says "when I get this report it does not usually end at O1900". But his (or her) method includes building a formula in O2 then copying it down "as far as", which suggests to me that when he (or she) gets the report column O is actually blank. In which case, sizing the formula range by counting the values in column O is a non-starter.

I'd use another column to get the vertical dimension, e.g.

Range("O2").Resize(WorksheetFunction.CountA(Range("N:N"))-1, 1).FormulaR1C1 = "=(RC[-6]+RC[-1])*RC[-5]"

of course, it's nowhere near as elegant as how you'd do it, but you probably get my drift.

Rgds,
Andy

Note the -1 in red -- I'm assuming that row1 contains headers, thus whatever's in N1 needs to be "uncounted".

#### Zack Barresse

Good catch Andy.

And I think it's quite elegant. Just a different means to the same end.

#### JUGGERNAUT21

It's working! Appreciate the assistance firefytr & Bomb#21

#### bomb #21

JUGGERNAUT21 said:
It's working! Appreciate the assistance firefytr & Bomb#21
Got your PM, thanks for the feedback. You can use "Thread Tools" to mark this as "Solved" if you wish.

Rgds,
Andy

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On