Max Formula Returning a Figure

[kaskytrinh]

I have a ridiculously crazy worksheet that has complex VBA. There are a number of changes that I’m having to make, and I’m stuck on what should be a simple formula right now. This is just one of the 20+ scenarios that it runs through, and what I want to do at this iteration is a max formula in column BC that looks at column BB, for the cells within this scenario, “BB” & Count:”BB” & Count + 1. I can’t for the life of me figure out how to do it. My formula ends up with the text versus actually naming the cell range.

I also tried using an “EndRow” reference, but no luck there either. I’ll accept any thoughts, opinions, far-fetched possibilities and the like that you may have to offer. Thanks!

ElseIf (OptConcatO = OptConcatTw And OptConcatTw <> OptConcatTh) And OType = "WRITEPUT" And NOType = "BUYPUT" Then
Range("A" & Count) = "Put Spread"
Range("BB" & Count).FormulaR1C1 = "=VALUE(REPLACE(TRIM(LEFT(RIGHT(SUBSTITUTE("" ""&RC[-50],"" "",REPT("" "",300)),300),300)),1,1,""""))"
Cells(Count, 25).Value = Contract
Range("BA" & Count) = "Put Spread"
Cells(Count + 1, 25).Value = NContract
Range("BA" & Count + 1) = "Put Spread"
Range("BB" & Count + 1).FormulaR1C1 = "=VALUE(REPLACE(TRIM(LEFT(RIGHT(SUBSTITUTE("" ""&RC[-50],"" "",REPT("" "",300)),300),300)),1,1,""""))"
Dim EndRow As Range
EndRow = Range("BB" & Count + 1)
'Having difficulty with Min/Max formulas needed for exposure calcs - needs to be from BB & Count to BB & Count + 1
'Range("BC" & Count).FormulaR1C1 = "=MAX(RC[-1]:RC[-1] & EndRow)"
Count = Count + 2

 

[RickXL]

Hi,

Does this work?

Range("BC" & Count).FormulaR1C1 = "=MAX(RC[-1]:R[+1]C[-1])"