Maximum value

[Hatye]

Hi,

In column A I've got dates and in column B I've got different text strings.
I would like to find the most recent text string (maximum value in A with corresponding value in B). This to be inserted into a comment, such as this:

SisteRev =


Range("J10").ClearComments
Range("J10").AddComment
Range("J10").Comment.Text Text:="Siste rev.:" & Chr(10) & SisteRev

Where "SisteRev" should be the most recent text string. Can anyone help me with this?

 

[Andrew Poulsom]

Try eg:

SisteRev = Evaluate("=INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,FALSE))")

 

[Hatye]

Try eg:

SisteRev = Evaluate("=INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,FALSE))")

Hi Andrew Poulsom and thank you for the reply!
I'm using the Norwegian version of Excel, and I have found the Norwegian formula values of 'index', 'match' and 'max'. But is 'Evaluate' a formula (which should be translated) or is it a code (which should work as written)?
When I translate the formula (without 'Evaluate') and insert it into a random excel cell, it returns correct text string. But when I use =INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,FALSE) in the comment box, it shows the formula as written.

I hope you understand what I mean, and that you can help me with this

 

[Andrew Poulsom]

Evaluate is a VBA function. You shouldn't need to translate it.

 

[Hatye]

Hmm, that's strange.. This is what I've got:
SisteRev = Evaluate("=INDEKS(Versjon!C2:C999;SAMMENLIGNE(STØRST(Versjon!A2:A999);Versjon!A2:A999);USANN)")

Where:
INDEKS = INDEX
SAMMENLIGNE = MATCH
STØRST = MAX
USANN = FALSE

Values are placed in the sheet 'Versjon' and the text string are in column C instead of B.

When I try to run a macro with 'Evaluate([...])' I've got 'Run-time error '13': Type mismatch'. Even though I get correct value when I insert the formula into a random cell.

 

[Andrew Poulsom]

Have you tried using English function names? And VBA expects commas rather than semicolons as the argument separator.

 

[Hatye]

Have you tried using English function names? And VBA expects commas rather than semicolons as the argument separator.

Oh, now I feel so stupid. It worked out!

Thank you so much