help with VB formula

[RompStar]

trying to figure out this script, I understand most of it, the sFormula = part confuses me:

Just so you know, the Excel sheet that this VB works on has the following columns, this figures out the conformance levels.

A - Building number
B - Date
C - Yes / No
D - NC Reason
E - Status, Open, Closed, Closed - NC
F - NC Finish Date

Basically, the data entry person, enters the date, yes or no into C, if the pickup was late, then reason why in D, E status of transaction, and F has late date closing. NC = Non-Conformance

This script looks at the data and figures out the compliance percentage, script below:

I am looking at it:

sFormula = "SUMPRODUCT((C9:C" & iLastRow & "=""Yes"")*" & "(SUBTOTAL(3,OFFSET(B9:B" & iLastRow & ",ROW(B9:B" & iLastRow & _
")-MIN(ROW(B9:B" & iLastRow & ")),,1))))"

IS this right ?

Can some exaplin the sFormula, what order does it work in ? I know it looks in C and B, looks at the Date Range and compares to Yes or something like that..













Option Explicit

Sub MacroSpecial()
Dim iLastRow As Long
Dim sFormula As String
Dim dteStart As Date
Dim dteEnd As Date
Dim sDateFormat
Dim cMatches As Long

' This is the script that does the Metrics. Current.

dteStart = InputBox("Supply start date")
If dteStart = 0 Then
Exit Sub
Else
dteEnd = InputBox("Supply end date")
If dteEnd = 0 Then
Exit Sub
End If
End If
sDateFormat = Range("B9").NumberFormat
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("B8:B" & iLastRow)
.AutoFilter Field:=1, _
Criteria1:=">=" & Format(CDate(dteStart), sDateFormat), Operator:=xlAnd, Criteria2:="<=" & Format(CDate(dteEnd), sDateFormat)
cMatches = .SpecialCells(xlCellTypeVisible).Count - 1


sFormula = "SUMPRODUCT((C9:C" & iLastRow & "=""Yes"")*" & "(SUBTOTAL(3,OFFSET(B9:B" & iLastRow & ",ROW(B9:B" & iLastRow & _
")-MIN(ROW(B9:B" & iLastRow & ")),,1))))"


MsgBox cMatches & ", " & Format(Evaluate(sFormula) / cMatches, "0.0%") & "as Yes"

Range("H3").Value = Format(Evaluate(sFormula) / cMatches, "0.0%")
.AutoFilter
End With

' write in the search date values for reference into the sheet
Range("H5").Value = dteStart
Range("I5").Value = dteEnd
Range("J8").Value = cMatches

' write the time stamp of this search into the sheet cell
Range("H8").Value = "Search Last Run: " & Now


End Sub

 

[RompStar]

when I press debug it points to the:

sFormula = WorksheetFunction.SumProduct(Range(Cells(9, 3), Cells (iLastRow, 3)) = "Yes", _
WorksheetFunction.Subtotal(3, WorksheetFunction(Range(Cells(9, 2), Cells(iLastRow, 2)) - _



------> pointing to this WorksheetFunction.Row(Range(Cells(9, 2), Cells(iLastRow, 2))), , , 1)))

with some help from my friend, me and mostly him wrote the code, I started about half of it and he ended the rest, I am still learning...

 

[XLGibbs]

Well, it looks like the formula is placed in as part of the visible cells following the filter, so the original code (assuming it worked properly there) will need to be restored.

to have place where the sformula result is placed
would require only this (per your original question) once the original is restored...

I will set up a sample sheet that may be able to point me to the type mismatch error, but the coded version of the formula worked fine on my sample of only about 10 rows...(for that piece only)...and I did not test or see how the whole picture laid out..

Range("K8").Value = Evaluate(sFormula)

I think.....I am no expert either...

 

[RompStar]

Thanks for helping me out and taking the time in the first place :- ) I always offer to buy beer to everyone.



Thanks!