Hi hiker95,
I have now uploaded the xlsx sheet to BOX. Thank you for that, so easy. This is the link:
https://www.box.com/s/38nnpcxvli8wzvvzgdmd
When you open the document you will see (hopefully if formatting is the same) three red highlighted fields (Field Z = FDT_FIELD_, FIELD AB = FDT_DO_PRO, FIELD AL = ENTEROCOCC). These are the three I need to calculate for the entirety of the document with the formulas within the cells Z5: Z7, AB5:AB7 and AL5:AL7).
If you can't access the forumla this is what I'm looking for. I'm sure if I have code for one column I could try to make sense of the others - should be very similar.
Observations =COUNT(Z2:Z3) *Count number of occurences until change of value / station)*
Viol =SUM(COUNTIF(Z2:Z3,"<6"),COUNTIF(Z2:Z3,">9")) *Sum of all less than 6 and greater than 9 , account for all until change of station*
Viol Rate =(Z6/Z5)*100 *Viol/Observations x 100 for percentage outcome*
Below I have your modified code, of course the request I have now can be separate or anyway that is simpler. Thank you for any help!
***********************
Code used previously:
Option Explicit
Sub InsertBetweenV2()</SPAN>
' r is a counter</SPAN></SPAN>
' lr is the lastrow</SPAN></SPAN>
' i is an array to hold the inserted text</SPAN></SPAN>
Dim r As Long, lr As Long, i</SPAN>
' turn screen updating OFF</SPAN></SPAN>
Application.ScreenUpdating = False</SPAN>
' initialize the i array</SPAN></SPAN>
i = Array("", "OBS", "VIOL", "VIOL RATE", "STATEMENT", "")</SPAN>
' find the last used row in column "A" and add one to lr</SPAN></SPAN>
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1</SPAN>
' when inserting/deleting rows it is good practice to start from the bottom and go up</SPAN></SPAN>
' loop from one cell down from the last cell in column A to row 3</SPAN></SPAN>
' the "Step - 1" means loop in reverse down to up</SPAN></SPAN>
For r = lr To 3 Step -1</SPAN>
' if cell A7 is not equal to cell A6 then</SPAN></SPAN>
If Cells(r, 1) <> Cells(r - 1, 1) Then</SPAN>
' at cell A7 insert 5 blank rows</SPAN></SPAN>
Rows(r).Resize(6).Insert</SPAN>
' transpose the i ary at A7</SPAN></SPAN>
Cells(r, 1).Resize(6).Value = Application.Transpose(i)</SPAN>
' the first inserted row is blank and its interior color should be GRAY</SPAN></SPAN>
Cells(r, 1).Resize(, 46).Interior.ColorIndex = 15</SPAN>
' in the six inserted rows, the three rows with the inserted text,</SPAN></SPAN>
' row 2 thru row 5 should have the text as BOLD</SPAN></SPAN>
Cells(r + 1, 1).Resize(4).Font.Bold = True</SPAN>
' the last of the five inserted rows is blank and its interior color should be GRAY</SPAN></SPAN>
Cells(r + 5</SPAN>, 1).Resize(, 46).Interior.ColorIndex = 15</SPAN>
End If</SPAN>
Next r</SPAN>
' auto fit column A the the widest text string added</SPAN></SPAN>
Columns(1).AutoFit</SPAN>
' turn screen updatting back ON</SPAN></SPAN>
Application.ScreenUpdating = True</SPAN>
End Sub</SPAN>
