Dynamic Formula for Percentages

[Belair58]

Hello,

I have a sheet that I've created a blank row after each change in Column J. I need to have column P in that row to have the formula, but dynamic values for the column O should be dynamic : =ROUND(COUNTIF($O$2:$O$1007,"Late")/COUNTA($O$2:$O$1007)*100,2) at each blank line. I've got the do while loop created to paste in the formula, but I have no idea how to tell the code to only look at the range in column O that corresponds to the number of rows that are actually in that section.

So the code =ROUND(COUNTIF($O$????:$O$????,"Late")/COUNTA($O$????:$O$????)*100,2) needs to be dynamic. I've thought of looking for a blank cell, which each section except for the first would have, but not certain how to write that into the formula.

Any help would be greatly appreciated

 

[DanteAmor]

Do you need something like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:78.89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >O</td><td >P</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >Head</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >v2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >late</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >v2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >late</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >v2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >late</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >v2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="text-align:right; ">42.86</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >late</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >v3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >late</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td style="text-align:right; ">66.67</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td >v4</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td >v5</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td >late</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td >late</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td >v5</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td style="text-align:right; ">50</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >P9</td><td >=ROUND(COUNTIF($O$2:$O$8,"Late")/COUNTA($O$2:$O$8)*100,2)</td></tr></table></td></tr></table>

How about:

Sub Macro5()
  Dim c As Range
  For Each c In Range("O2:O" & Range("O" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    c.Cells(c.Rows.Count, 1).Offset(1, 1).Formula = "=ROUND(COUNTIF(" & c.Address(1, 1) & ",""Late"")/COUNTA(" & c.Address(1, 1) & ")*100,2)"
  Next
End Sub

 

[Belair58]

Dante,

That appears to be exactly what I need, but when I run it I get a 1004 error "No cells were found".

 

[DanteAmor]

Dante,

That appears to be exactly what I need, but when I run it I get a 1004 error "No cells were found".

Run the macro on the sheet where you have your information, you must have information in column O. The formulas the macro puts them in column P.

Do you have the data as in my example?
Do you have empty rows, that is, empty cells without spaces or does it contain formulas?

 

[Belair58]

Dante,

I created a new test sheet and it worked great. So I copied and pasted my working sheet as values and it worked great. There's something in my data that's preventing it from running. I'll figure it out, thank you so much for the formula.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.