Using the & correctly in formula

[NatalieW12]

Hi All,

I have the below code that works (FindMax), but thought I would try and alter it by replacing the “B20:B” part with FR. I’m not having any luck, can someone point me in the right direction please.


This works

Please Login or Register  to view this content.

This does not – the Range("e6").Formula = "=Max(" & FR & LR & ")” part is wrong, it is missing the colon: and don’t know how to make it work.

Please Login or Register  to view this content.

Thanks
Nat

[BadlySpelledBuoy]

If FR is always static as B9, then why replace it in the formula at all?

But if you really want to then you could use this:

Please Login or Register  to view this content.

BSB

[BanginMyHeadOnMyDesk]

Hi Nat,

Note in your original code you're declaring FR as Long but then setting it as a range. This is confusing matters slightly.

BSB beat me to a solution but mine wasn't much different.

Beth.

[jindon]

Please Login or Register  to view this content.

[NatalieW12]

Thank you all,

BSB,
you are correct, why replace it - I just thought I would try and make it easier, but not really so.

Beth, Y
es, I found that out - that Long was not correct.

Jindon
Not sure how it works with the resize, but it does

Again, thank you all for your help

Cheers
Nat

[BadlySpelledBuoy]

Glad we could help

BSB

[jindon]

Jindon
Not sure how it works with the resize, but it does

It works based on the first cell, so doesn't need to know the column as long as LR is given.