wie kann ich in einem bestimmten Zeilenbereich (z.B. H40:O40), in dem in jeder einzelnen Zelle aus diesem genannten Bereich Text und Zahlen gleichzeitig stehen, die höchste zusammenhängende Zahl (also den Maxwert in Zelle $A$40 auslesen?
Function Hoch(Bereich As Range)
Dim objRegEx As Object, objMatch As Object
Dim Zelle As Range, Wert As Long
For Each Zelle In Bereich
Set objRegEx = CreateObject("VBScript.RegExp")
With objRegEx
.Pattern = ("\d+")
Set objMatch = .Execute(Zelle)
If objMatch.Count > 0 Then
Wert = Application.Max(objMatch(0).Value, Wert)
End If
End With
Next
Hoch = Wert
End Function
A | B | C | D | E | F | |
1 | 223 | a222b | b45 | z223b |
verwendete Formeln | |||
Zelle | Formel | Bereich | R1C1 für Add In |
A1 | =Hoch(B1:E1) | =Hoch(RC[1]:RC[4]) |
Function Hoch(Bereich As Range)
Dim objRegEx As Object, objMatch As Object
Dim Zelle As Range, Wert As Long, i as long
For Each Zelle In Bereich
Set objRegEx = CreateObject("VBScript.RegExp")
With objRegEx
.Pattern = ("\d+")
Set objMatch = .Execute(Zelle)
If objMatch.Count > 0 Then
For i = 0 to (objMatch.Count -1)
Wert = Application.Max(objMatch(i).Value, Wert)
next i
End If
End With
Next
Hoch = Wert
End Function
Option Explicit
Private Function fncExNumber(rngRange As Range) As Double
Dim objRegEx As Object
Dim objMatch As Object
Dim rngCell As Range
Dim lngIndex As Long
Dim lngTMP As Long
Set objRegEx = CreateObject("VbScript.RegExp")
For Each rngCell In rngRange
With objRegEx
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "\d+"
Set objMatch = .Execute(rngCell)
End With
If objMatch.Count > 0 Then
For lngIndex = 0 To objMatch.Count - 1
lngTMP = Application.Max(objMatch(lngIndex).Value, lngTMP)
Next lngIndex
End If
Next rngCell
Set objRegEx = Nothing
Set objMatch = Nothing
fncExNumber = lngTMP
End Function
Aufruf:
=fncExNumber(A1:C20)
Range anpassen.