# Max Formula Returning a Figure

##### New Member
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### RickXL

##### MrExcel MVP
Hi,

Does this work?

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

Replies
6
Views
246
Replies
5
Views
373
Replies
3
Views
99
Replies
13
Views
764
Replies
4
Views
200

1,195,682
Messages
6,011,132
Members
441,586
Latest member
rodsin76

### 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.

### Which adblocker are you using?

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

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