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

Thread Starter
Joined
Dec 28, 2005
Messages
2
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
 
Joined
Jul 25, 2004
Messages
5,458
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
??
 
Joined
Jul 1, 2005
Messages
8,546
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".
 
Joined
Jul 1, 2005
Messages
8,546
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top