Excel Automate - get highest number in filtered Column

Dear Shakiru,
thanks for your Feedback!
But i think this one is written for VBA and works great there.

I have Problems to get this number in Excel Automate with Office Scripts.

There is get multiple Errors:

const rng1 = sourceSheet.Range("M1:M" & usedRange)
Set filteredRng = rng1.SpecialCells(xlCellTypeVisible)
maxVal = Application.WorksheetFunction.Subtotal(104, filteredRng)

42, 27] Property 'Range' does not exist on type 'Worksheet'.

1. [42, 33] The left-hand side of an arithmetic operation must be of type 'any', 'number', 'bigint' or an enum type.
2. [43, 6] Cannot find name 'filteredRng'.
3. [43, 38] Cannot find name 'xlCellTypeVisible'.
4. [44, 2] Cannot find name 'maxVal'.
5. [44, 11] Cannot find name 'Application'.
6. [44, 55] Cannot find name 'filteredRng'.
   

Hi MaxMeyer.!

Please try this
Sub GetHighestNumber()
Dim rng As Range
Dim filteredRng As Range
Dim maxVal As Variant

Set rng = ThisWorkbook.Worksheets("Sheet1"). Range("A1:A100") ' Replace with your range
Set filteredRng = rng. SpecialCells(xlCellTypeVisible)
maxVal = Application.WorksheetFunction.Subtotal(104, filteredRng)

' Store the maximum value in cell M28
ThisWorkbook.Worksheets("Sheet1"). Range("M28"). Value = maxVal
End Sub

In this script, we first define the range we want to find the highest number in (rng). We then use the SpecialCells method to get only the visible cells after the filter is applied (filteredRng). We then use the Subtotal function with the xlMax function number (104) to find the highest number in the filtered range. Finally, we store the maximum value in cell M28.

You can customize this script to fit your specific needs. Just make sure to update the range references and worksheet names as necessary.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards,
Shakiru

i need to get the Highest Scores from Cell BB6 to BD27 which are merged , and after achieving the result i need that how many Balls have been Played to reach this Score , and then if Possible to get the Player Name , can any One help

At first this is the German forum, the English one is here:

http://answers.microsoft.com/en-us/office/forum/excel

You missed to specifiy the search criterium for MATCH.

If you have merged cells you have to change the selected cell references to the first column:

G73: =INDEX(AZ6:AZ27,MATCH(F73,BB6:BB27,0))

So the player name is in the same row:

I73: =INDEX(E6:E27,MATCH(F73,BB6:BB27,0))

Andreas.

ähhh. Claus noch ne Frage

Get das auch in VBA

mit

Columns("U:U").Select

Selection.TextToColumns Destination:=Range("U1"), DataType:=xlFixedWidth, _

FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

klappts nicht.

Thom

Was kann ich noch machen! Ich habe glaube ich in meinem Account keine Telefonnummer hinterlegt gehabt wenn ja ist es evtl. noch meine Alte Rufnummer
<br />die ich nicht mehr habe! Wie komme ich an mein Account?
<br />What else can I do! I don't think I had a phone number stored in my account, if so, it may still be my old phone number that I no longer have! How do I get my account?
<br />How can I be activated again?
<br />