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
How do i extract values in a String?
- trippyninjas
- Thread is marked as Resolved.
-
-
The problem is the data is not values but strings
-
Hi Trippy,
I found some code at https://stackoverflow.com/ques…ind-numbers-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.
Display MoreCode- 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
-
-
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
