Jibbadiah
James
- Local time
- , 10:53
- Joined
- May 19, 2005
- Messages
- 282
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!! ;-(
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
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!! ;-(
Code:
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