Find all currency values between a specified range (1 Viewer)

[Jibbadiah]

Hi everyone... it's been a while... Hope that you're all well.
Using Excel 2002 on Windows XP...
I'm Attempting to loop through 25000+ rows in a column and check if the cell value is between a given currency range.
I will then save the row number of each matching record in an array and allow the user to click a control to view the matching record (on a different worksheet template... which will maintain focus throughout).
My biggest problem is that the existing Currency values are being stored as text. I can't find an easy way to overcome this, instead I end up doing ridiculous string functions for each record to account for positive/negative values and the currency symbol. Can someone please suggest a better solution?

Spreadsheet value: £2,404.89
VBA equivalent value: "£2,404.89"

The following code works fine, but it can't be the best solution because it looks pants!! ;-(

    Dim lngCellCount As Long
    Dim strLastPopRow As String
    Dim strColLetter As String
    Dim strVal As String
    Dim curMin As Currency
    Dim curMax As Currency
    Dim curVal As Currency
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCount As Long
    arrSearch = Array()
    
    On Error GoTo PROC_ERROR
    
    lngCount = 0
    curMin = 1000 'representing £1,000.00
    curMax = 2000 'representing £2,000.00
    lngRow = 2
    strColLetter = "S"
    lngCol = Columns(strColLetter).Column
    
    strLastPopRow = Sheets("Sheet1").Range(strColLetter & "65536").End(xlUp).Row
    lngCellCount = strLastPopRow - 1 'Account for header row
    
    For lngRow = 2 To lngCellCount 'Loop from 1st record to last populated record
        strVal = Sheets("Sheet1").Cells(lngRow, lngCol).Value 'Gets a value for current selected record
            If Left(strVal, 1) = "-" Then 'Account for minus value (keeping minus sign)
                strVal = "-" & Right(strVal, Len(strVal) - 2)
            Else
                strVal = Right(strVal, Len(strVal) - 1) 'Remove £ sign
            End If
        curVal = CCur(strVal) 'Change string to currency format for proper comparison
        If curVal > curMin And curVal < curMax Then 'Make sure value is between required range
            lngCount = lngCount + 1 'Count total number of records that fall in range
            'Add values to Array
            ReDim Preserve arrSearch(UBound(arrSearch) + 1)
            arrSearch(UBound(arrSearch)) = lngRow - 1
        End If
    Next lngRow

Please note: I know that this is easier in Access but endusers don't have it... they only have Excel.

Any help much appreciated.

Cheers,

James

 

[DCrake]

When you say "Click a control" what control are you referring to?

I would have thought that using conditional formatting on the value field to display the cell a different color would highlight the appropriate rows.

Or am I missing something?

David

 

[Jibbadiah]

Yeah... conditional formatting would work to highlight these differences on a displayed worksheet but I want to retrieve the records to a different location. So I want to create a "find values between range" functionality that allows me to put the row numbers into an array and then allow the user to step through records that meet their selection.

I have included a jpg to show how the front of this template looks.

My sample values for testing are 1k-2k, that is why the found text doesn't match the combo value in the picture.

Template views records (which has focus)
Records are on Separate worksheet
User requests to see only records matching specific value criteria

I realise that this is a little complicated and not really how others use Excel, but I'm hoping that I can improve my code the best that I can.

J.

 

[DCrake]

What happens if you create a new worksheet that references the currency column and has been formatted to double instead of currency then in your combo box have another column (width set to 0) that is the numeric equivelant of the currency range then use these to parameters to biuld you criteria?

Just a thought

David

 

[Jibbadiah]

David... the previous code takes about 2 seconds to to run through over 25000 records... so although it looks ugly it still works... your suggestion would probably work, but I was after a "better solution"... not a solution that seems as protracted as my original!!

The jpg image actually shows a template that will be used to create 1000+ reports and the functionality will work automatically as soon as the data is output... adding another reference column will create an unwanted increase in file size. I already have a nice search form for text based searches across all fields that use the inbuilt find function, but I can't use it to find values between a range (at least not easily... there was something nice mentioned on Ozgrid.com but it was difficult to implement for my situation)

Thanks for the feedback though... all comments are good comments.

Any other takers?

Cheers

J.