Creating and copying a formula with Macros till last row

[hilty06]

I have a table with data in it that has headers and a total line at the bottom. I want to create a formula with macros that uses data from the current row and the last row. I want to copy that formula to every cell in my column except the last row (because it's the total line). Then I want to sort my data with the values created by the formula. I've tried a bunch of different things and looked at a lot of online help guides without any success.

One more thing. I need this macro to be able to work with excel spreadsheets of varying length. Sometimes there are only 50 or so rows and sometimes there are over 70. The columns are always the same. Thanks for your help.

 

[rdw72777]

This is probably the code you are looking for to find the unique number of rows. This assume column A is the column with your data

myOpenendrow = Range("A" & Rows.Count).End(xlUp).Row

From there, you can just run a simple for/next loop to do whatever it is you are trying to do. I'm guessing it is a percentage formula but you didn't include the information or any code/formulas you have so this is our jumping off point?

 

[hilty06]

My formula is just adding divisions of the row number by the total number for three different columns. I'm not so great with macros. How would you set up a loop to put this formula in each one of the needed cells?

 

[rdw72777]

Can you give me some references like sample data, which data is in which column, what calculation do you wish to do, etc.

Right now we only know that you want to do a claulcation but we don't know which one, on what range, etc. More details will help a ton

 

[hilty06]

Ok sample data. I'll try to type it like a table and let's hope it makes sense.

I want a formula in the column after Sales/Hour (G) that gives a ranking for the person. For Bill lets say it's =SUM(B2/B$7+E2/E$7+F2/F$7) and for John =SUM(B5/B$7+E5/E$7+B5/F$7)

It needs to recognize the bottom row (total) because there aren't always the same number of rows. Eventually once I have my "ranking" for each person based on the formula I want to sort the spreadsheet by their ranking without moving the total line. Thanks for your help!

 

[hilty06]

Here's the table I tried to paste:

<table style="border-collapse: collapse; width: 270pt;" border="0" cellpadding="0" cellspacing="0" width="359"><col style="width: 33pt;" width="44"> <col style="width: 44pt;" width="58"> <col style="width: 29pt;" width="39"> <col style="width: 45pt;" width="60"> <col style="width: 58pt;" width="77"> <col style="width: 61pt;" width="81"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 33pt;" width="44" height="20"> </td> <td class="xl65" style="border-left: medium none; width: 44pt;" width="58">Hours </td> <td class="xl65" style="border-left: medium none; width: 29pt;" width="39">Sales</td> <td class="xl65" style="border-left: medium none; width: 45pt;" width="60">Contacts</td> <td class="xl65" style="border-left: medium none; width: 58pt;" width="77">Conversion</td> <td class="xl65" style="border-left: medium none; width: 61pt;" width="81"> Sales/Hour</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Bill </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">33%</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0.25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Tom </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">50%</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0.5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Sarah </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">15</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">40%</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0.75</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">John </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">100%</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0.5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Ryan</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">67%</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Total </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">32</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">21</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">40</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">53%</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0.66</td> </tr> </tbody></table>

 

[hilty06]

I've been playing around and I can now make the formula with my macro. I just need to be able to copy my formula (in cell T3) to the second to last row.

 

[rdw72777]

hilty, can you post the code you have, then we can modify it to do the last step (pasting down to the next to last row)?

 

[hilty06]

Here is what I have. I know there's probably a better way to determine the last row, but I have the macro create a count function and then use that for the last row then have the macro delete it. I put a note where I need to insert the command to sort the data. Thanks for your help! Code:

Sub Rankings()
'
' Macro1 Macro
'

'
With Application
.ScreenUpdating = False
End With

range("U3").Select
ActiveCell.FormulaR1C1 = ""
range("Q1:R1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
range("U3").Select
ActiveCell.FormulaR1C1 = "=COUNT(C[-3])"

Dim numcallers As Integer
Dim numcallers2 As Integer

numcallers = range("U3").Value + 2
numcallers2 = numbcallers - 1

range("U4").Select
ActiveWindow.SmallScroll Down:=-3
range("T3").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-6]/R" & numcallers & "C[-6]+RC[-3]/R" & numcallers & "C[-3]+RC[-2]/R" & numcallers & "C[-2])"
range("T3").Select
Selection.NumberFormat = "0.00"
Columns("T:T").EntireColumn.AutoFit


range("T3").Copy range("T3", range("T" & range("R" & Rows.Count).End(xlUp).Row))


range("U3").Select
Selection.ClearContents
Cells(numcallers, 20).Select
Selection.ClearContents


'This is where I need to sort by Column T from B3 to the second to last row of T (this row has already been determined and that number is numcallers2). Or just all rows from 3 to numcallers2 by once again column T.


range("Q1:R1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
range("A1").Select

End Sub

 

[rdw72777]

hilty, ok I'll take a look.

One question though, will the last row always be labeled "Total". If so, instead of hardcoding the row, couldn't your formula become (for John in Col G)

=SUM($B2/VLOOKUP("Total",$A$1:$F$100,2,0)+$E2/VLOOKUP("Total",$A$1:$F$100,5,0)+$F2/VLOOKUP("Total",$A$1:$F$100,6,0))

This way your formula would always be the same, up to 100 rows of data. then the only real issue is the ranking and the sorting.