I need to be able to display the highest value and lowest value within a range.
Could anyone develop a module to make this possible?
I have attached the excel file below.
Thank you
I need to be able to display the highest value and lowest value within a range.
Could anyone develop a module to make this possible?
I have attached the excel file below.
Thank you
The problem is the data is not values but strings
Hi Trippy,
I found some code at https://stackoverflow.com/questions/7239…s-from-a-string and had a play to create some UDFs. The code and attachment is below. Although what I have done works on the ranges you gave me it has a couple of shortfalls I have not been able to rectify, maybe someone else here could fix them - if you have two high values of the same magnitude in the list it will fail, I assume the same for low value. I will have another look later but am not hopeful.
There are three UDFs, "OnlyHigh", "OnlyLow" and (MaxHVals) - OnlyLow the use of each is demonstrated on the attachment. You will probably find some bugs in them, just post back when they pop-up. They should all work for kHz, MHz, GHz and THz.
Function MaxHVals(RNGE) As String
Dim MyLow, MyHigh As String
MyLow = OnlyLow(CStr(RNGE(1)))
MyHigh = OnlyHigh(CStr(RNGE(1)))
For x = 1 To RNGE.Rows.Count
If OnlyLow(CStr(RNGE(x))) < MyLow Then MyLow = OnlyLow(CStr(RNGE(x)))
If OnlyHigh(CStr(RNGE(x))) > MyHigh Then MyHigh = OnlyHigh(CStr(RNGE(x)))
Next x
For x = 1 To RNGE.Rows.Count
If OnlyLow(CStr(RNGE(x))) = MyLow Then MyLow = RNGE(x)
If OnlyHigh(CStr(RNGE(x))) = MyHigh Then MyHigh = RNGE(x)
Next x
MaxHVals = left(MyLow, InStr(1, MyLow, "-")) & Right(MyHigh, Len(MyHigh) - InStr(1, MyHigh, "-"))
End Function
Function OnlyHigh(s As String) As Double
' Variables needed (remember to use "option explicit"). '
Dim retval As String ' This is the return string. '
Dim i As Integer ' Counter for character position. '
Dim mult As Double
' Initialise return string to empty '
retval = ""
s = Right(s, Len(s) - InStr(1, s, "-"))
If InStr(1, s, "k", vbTextCompare) Then mult = 1000#
If InStr(1, s, "M", vbTextCompare) Then mult = 1000000#
If InStr(1, s, "G", vbTextCompare) Then mult = 1000000000#
If InStr(1, s, "T", vbTextCompare) Then mult = 1000000000000#
' For every character in input string, copy digits to '
' return string.
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next
' Then return the return string. '
OnlyHigh = CDbl(retval) * mult
End Function
Function OnlyLow(s As String) As Double
' Variables needed (remember to use "option explicit"). '
Dim retval As String ' This is the return string. '
Dim i As Integer ' Counter for character position. '
Dim mult As Double
' Initialise return string to empty '
retval = ""
s = left(s, InStr(1, s, "-") - 1)
If InStr(1, s, "k", vbTextCompare) Then mult = 1000#
If InStr(1, s, "M", vbTextCompare) Then mult = 1000000#
If InStr(1, s, "G", vbTextCompare) Then mult = 1000000000#
If InStr(1, s, "T", vbTextCompare) Then mult = 1000000000000#
' For every character in input string, copy digits to '
' return string.
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next
' Then return the return string. '
OnlyLow = CDbl(retval) * mult
End Function
Display More
option explicit is not set as noted in the code - could cause an issue or two as things not declared fully/correctly
Make sure you correctly turn on error back 'on', what that code does is ignore errors
jiuk
Don’t have an account yet? Register yourself now and be a part of our community!