# Converting Cell Formula (MAX) to VBA Formula

#### Rufus Clupea

##### Board Regular
Hi Folks,

Just a quickie (I think/hope)... =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.

• 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:

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

#### Rufus Clupea

##### Board Regular
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

Wow, this is the first time I've seen that notation! 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? I'm having a little trouble getting my head around/understanding that.

Guess I've got some more reading/hair-pulling ahead... 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
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

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

#### Rufus Clupea

##### Board Regular
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

Aha, YES! 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
Re: Need Help Converting Cell Formula (MAX) to VBA Formula

You're welcome & thanks for the feedback

#### Rufus Clupea

##### Board Regular
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: