Converting Cell Formula (MAX) to VBA Formula

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85
Hi Folks,

Just a quickie (I think/hope)... :rolleyes:

I had a formula in a spreadsheet cell:
=TRUNC(MAX(((MAX(E5-5,(E8-4)))/10),1))

that I needed to move into code, i.e.:
Sheet1.Range("J18") = TRUNC(Max(((Max(E5 - 5, (E8 - 4))) / 10), 1))

The VBE/Compiler doesn't like it in this form.

After reading several similar questions/threads, I've tried:
  • Putting Sheet1.Range and quotes around E5 & E8
  • Putting WorksheetFunction. in front of the 2 Maxes
  • Using: MyValue = Application.WorksheetFunction.Max()

but I don't think I'm implementing it correctly. (I tried defining it as a function).

Code:
Public Function MyValue()
MyValue = Application.WorksheetFunction.Max()[FONT=verdana]

Sheet1.Range("J18") = TRUNC(MyValue(((Myvalue(E5 - 5, (E8 - 4))) / 10), 1))
[/FONT]
I've also tried every combination of the above that I could think of.

I'm stumped.
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

If you just want the value, try
Code:
Range("J18").Value = [TRUNC(MAX(((MAX(E5-5,(E8-4)))/10),1))]
 

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

Wow, this is the first time I've seen that notation! :eek:

Of course it worked; I'm just not sure why...
The page I first looked it up on says:
... the square brackets are a replacement for the Range/Parentheses/Quotation Marks construct.
OK, then it goes on to say:
It can be used on either side of the equal sign.
Huh? :confused: I'm having a little trouble getting my head around/understanding that.

Guess I've got some more reading/hair-pulling ahead... :LOL:

Thanks for the tip.

Now, how do I ask this without sounding like a jerk... Is there another different solution I should know about for similar situations in the future? :)
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

With VBA there are always different ways to do something :LOL:
The [] are a shorthand version of
Code:
Range("J18").Value = Evaluate("TRUNC(MAX(((MAX(E5-5,(E8-4)))/10),1))")
 

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

Aha, YES! :biggrin:

Odd that neither of these... techniques(?) are even mentioned in the VBA books I have (or maybe not?) :banghead:

Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

You're welcome & thanks for the feedback
 

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

That should have been...
Odd that neither of these... techniques(?) are even mentioned in the VBA books I have (or maybe not so odd?) :banghead:
I think I'm collecting quite a bit of feedback for authors/potential authors (having done some writing myself in the distant past... :) ) Lots has to do with their indices (and things that should be there, but are hidden in obscure places... ;) )
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,354
Messages
5,443,966
Members
405,257
Latest member
daveyf

This Week's Hot Topics

Top