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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
The Sformula is a declared variable (named :sFormula for easier reading of the code) and represents the code equivalent of the Excel formula...

later in the code, it evaluates the formula an offers of a message box with the resulting count of "Yes" matches...

The Sumproduct formula is a pretty handy tool for creating values from a series of ranges (of matching size) compared to a known text...in the syntax shown, it evaluates true/false matches and the result of the Trues evaluate to 1X the number of occurrences, and the falses are omitted because the would be 0X the number of occurrences, 0 being the result of any false in the sequence.

obviously the needed ranges for comparison are the result of other nested formulas that server their own function to produce information required for the desired result.

Do you want it broken down step by step?
 
Upvote 0
It would be nice if you could run down the sFormula part only, it's sequence.

just this part:

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

The rest I get :- )

Like you said SFormula = evaluates like a formula in Excel, so that is the same as:

sFormula = "SUMPRODUCT((C9:C1272="Yes")*(SUBTOTAL(3,OFFSET(B9:B1272,ROW(B9:B1272)-MIN(ROW(B9:B1272)),,1))))"

Ok

This looks easier when looked at it this way.

So SUMPRODUCT is multiplied times SUBTOTAL ? that part I am lost on.

Please someone emplain this part:

(SUBTOTAL(3,OFFSET(B9:B1272,ROW(B9:B1272)-MIN(ROW(B9:B1272)),,1))))" :unsure:
 
Upvote 0
Lest say the last row is 12, for purposes of explanation: Formula would look like this...no lets strip away piece by piece

="SUMPRODUCT((C9:C12=""Yes"")*" &
"(SUBTOTAL(3,OFFSET(B9:B12,ROW(B9:B12)-MIN(ROW(B9:B12)),,1))))"


Each argument in a formula can be its own formula, with its own arguments so there are a number of nested functions in this one...

SumProduct ( Argument1, Argument2)

SUBTOTAL( type, ref1)
type 3 = CountA ( count occupied cells, regardless of errors)
Ref1 (range or cell to include) which in this formula is defined as:

OFFset(start, row, col, hieght, width)
ROW() identifies the row of the referenced cell(s)
MIN() identifies the minimum # in a range

which in your formula, the rows to offset is determined by subtractiing the row of the cell in the range from the minimum of the row in the range (thus making it sequential from 0 no matter what row the range starts on)

so in this example the ROW# of each cell in Range - the MINIMUM row in that range

SUMPRODUCT ((C9:C12 = "Yes" ----evaluate range C9:C12 for "Yes"

C9 = Yes
C10 = No
C11 = Yes
C12 = No

would be SumProduct (( {Yes,No,Yes,No} = "yes"

resulting in {1,0,1,0} due to coersion of true/false to 1/0

against the subtotal type 3 or COUNTA (occupied cells in range)

OFFSET(B9:B12, ROW(B9:B12)-MIN(ROW(B9:B12)) , , 1))

Syntax
Offset (starting , rows to offset, columns to offset , hieght of range, width of range)

so OFFSET(B9:B12, Row of range - minimum row# of range, 0 , 0, 1 column wide)

or

Offset({B9:B12}, {0,1,2,3}, 0,0,1) which ends up being the range B9:B12 in this example

so the resulting formula would end up being evaluated as

{1,0,1,0} times the subtotal the counting of cells occupied in {B9,B10,B11,B12}

so assuming:

B9 = 1
B10 = 3
B11 = 1
B12 = 4

Would result in a subtotal of 9, but subtotal type 3 is a countA so....

Therefore the formula would now be

{1,0,1,0} * {1,1,1,1} = 2

Of B11 were empty

{1,0,1,0} * {1,1,0,1} = 1


I am fairly certain I am now as confused as you! Hope that helps somewhat, but there is a lot happening in that formula
 
Upvote 0
ya, this is a good one, hahaha, I won't say that I understand it all :- ) but duhhhhh :LOL:

but I made a blank sheet, and I played around with column B and C, and now this makes a little bit more sense, visually anyways, not claiming to understand this 100%, boy, you did lost me, I have to re-read this several times.

Question: I want to output the value of the SFormula to a cell in the sheet..

Range("K8").Value = sFormula

but all I get is the formula that's printed into that cell, I want the result of that formula, what am I doing wrong ?
 
Upvote 0
Well, it is a matter of breaking down each formula to what it does, then applying it in position in the longer formula...

I tried anyway..
 
Upvote 0
Thank you for your time, I am not complaining, just some things take longer to grasp over here...

Range("K8").Value = sFormula

so if I want to write in the results of this formula into a cell of the sheet how do I do that so that the results are printed and not the formula it's self, which is what is happening right now.
 
Upvote 0
That is a whole nother issue. as the formula is currently written in its "excel" format so it works when placed in the sheet..


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


VBA result oriented format would be:


sFormula = WorksheetFunction.SumProduct(Range(Cells(9, 3), Cells(ilastrow, 3)) = "Yes", _
WorksheetFunction.Subtotal(3, WorksheetFunction(Range(Cells(9, 2), Cells(ilastrow, 2)) - _
WorksheetFunction.row(Range(Cells(9, 2), Cells(ilastrow, 2))), , , 1)))
 
Upvote 0
I get an error, type 13 mismatch, and when I press debug it highlight in yellow the sFunction = with the new code, I wanted to see how that worked.
 
Upvote 0
Would help if you posted exactly which line produced the error but these two would need to change

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

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


to this

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

Range("H3").Value = Format(sFormula / cMatches, "0.0%")

The Evaluate function was looking for a formula to evaluate, but the VB coded piece you requested is now producing the result....

I had not really looked at any other bits of code previously though, nor did I write the original, and I have not tested any of it.....but at a glance, this is what I see causing a potential problem.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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