help with VB formula

RompStar

Well-known Member
Joined
Mar 25, 2005
Messages
1,200
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
 
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...
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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...
 
Upvote 0
Thanks for helping me out and taking the time in the first place :- ) I always offer to buy beer to everyone.

:lol:

Thanks!
 
Upvote 0

Forum statistics

Threads
1,224,225
Messages
6,177,273
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top