How can i replace this "a" without application ran code?

[VBE313]

How can i replace the bold part?


Function test1234()
Dim a As Long
If ActiveSheet.Name = "Sheet1" Then
Else
End If
a = Application.Caller.Row + 1
Do While Cells(a, 1).Value <> "Yes"
If Cells(a, 2) = "Yes" Then Exit Do
test1234 = test1234 + Val(Cells(a, 2).Value)
a = a + 1
Loop
End Function

 

[Joe4]

Can you explaim exactly what it is that you are trying to do?

Typically, with Functions, you do not call things directly from ranges and sheets, you work off of variables arguments that part of a Function's inputs.
And with a function that you typically have a value that is ouput.

Sub Procedures are what you use to perform actions on ranges.

 

[VBE313]

Hi Joe4,

I am trying to calculate a bill of materials. For example... If it says "Yes" in column A, then calculate all of the ones below it until there is a "Yes" again. For column C, to get 19, the formula is "=IF(A1="No","",test1234()+B1)"

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Yes[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]19[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

OK, here is how I would do it.

So here is what my function look like (I documented it so you can see what each step is doing):

Function Test1234(critCell As Range, valCell As Range) As Double
'   critCell is the cell on the current row holding the Yes/No values
'   valCell is the cell on the crruent row holding the values

    Dim numYes As Long
    Dim i As Long
    Dim temp As Double
    
    Application.Volatile
    
    Do Until i = 5000   'exit if loop counter gets to 5000
'       Count number of Yes entries
        If critCell.Offset(i, 0) = "Yes" Then numYes = numYes + 1
'       Exit on second occurence of Yes
        If numYes = 2 Then Exit Do
'       Add to running sum
        temp = temp + valCell.Offset(i, 0)
'       Increment counter
        i = i + 1
    Loop
    
'   Set value to running sum
    Test1234 = temp

End Function

Then, I place this function is cell C1 and copy down for all rows:
=IF(A1="No","",Test1234(A1,B1))

 

[VBE313]

Joe4,

Thank you so much!!

 

[Joe4]

You are welcome!

 

[mikerickson]

You could do this without VBA

=IF(A1="no", "", SUM(OFFSET(A1,0,0,MATCH(A1,A2:A$65536,0),2)))

 

[VBE313]

Hi mikerickson,

Thank you for that! I have to use this code for 45 columns, which code would be faster?

 

[mikerickson]

Which was faster when you tried them on your spreadsheet?