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.

 

[hilty06]

Yeah, that does look cleaner. My code has been pastes from other places on the web until I get it to do what I want. My whole focus if functionality not necessarily the best code out there. But yes the last row always has the same name (Report Total). But for formatting column A is always empty so the Report Total is in Column B on the last row.

 

[rdw72777]

Here's a helpful hint for finding the last row on a worksheet.

LastRow = sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

This code will tell you the last row of data in column A. If you want the last row of data in a different column, then change the "A" in the code to the column letter you wish to look at.

 

[rdw72777]

Can you tell me exactly what cells your data is in? In your very first post you talk about columns A-F but your macro doesn't reference anything in those columns.

I'm struggling to figure out where your data is and what your macro is trying to do; if your data is in columns A-F then your macro makes no sense.

 

[hilty06]

Sorry if I haven't been clear.

There are two rows at the top which are the headers for the data. Column A is blank and so the data starts in column B. Column B has the name of all of the people with their statistics in the remaining columns till Column R. Column S is blank and the formula that calculates rankings is in T. So I want to sort the all of the data in the rows (names, stats, and rankings) by the final column (T) with the ranking in it. So the range for the sorting is all rows except the first two (so starting with 3) till the second to last row (numcallers2) not including the final row (numcallers) because it's the Report Total row.

Hopefully that clears things up. Let me know if you need any more info. Thank you so much for your help. There's no way I could figure this all out on my own.

 

[rdw72777]

Which column do you want the formula for the calculation to be in? I can put the ranking in column T as stated, so I'll assume that the calculation (B2/B7+E2/E7...etc) will go in column S.

 

[rdw72777]

Here, this should do what you want. The code assumes your sheet is "sheet1"

Sub Macro1()
'
' Macro1 Macro
Dim i As Integer
Dim LastRow As Long
'

'
LastRow = Sheets("sheet1").Cells(Rows.Count, "B").End(xlUp).Row

    For i = 4 To LastRow
    
        Cells(i, 19).FormulaR1C1 = _
            "=SUM(RC3/VLOOKUP(""Report Total"",R3C2:R100C7,2,0)+RC6/VLOOKUP(""Report Total"",R3C2:R100C7,5,0)+RC7/VLOOKUP(""Report Total"",R3C2:R100C7,6,0))"
        
        Cells(i, 20).FormulaR1C1 = "=RANK(RC19,R4C19:R" & LastRow - 1 & "C19,1)"
    
    Next i
    
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
    Key:=Range(Cells(3, 20), Cells(LastRow - 1, 20)), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Cells(3, 2), Cells(LastRow - 1, 20))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

 

[hilty06]

I'm getting an error message "Subscript out of range" for the
<LastRow = Sheets("sheet1").Cells(Rows.Count, "B").End(xlUp).Row> line of code.

If is scroll over everything in debug mode I believe that the error comes from the Rows.Count because it says Rows.Count=1048576. Thanks once again for your help!

 

[hilty06]

Got it to work! Thanks for all your help. My next project will be highlighting the cells with the largest value in the column and deleting rows if they have less than a certain value. If you'd like to point me in the right direction for these tasks that'd be great. Thanks again!