i am looking for a function that will check a cell to see if it has a currency code (e.g. GBP) inside it.
i am looking for a function that will check a cell to see if it has a currency code (e.g. GBP) inside it.
Any currency code? Is it in the cell or just part of format?Originally Posted by crmpicco
i am looking for a function that will check a cell to see if it has a currency code (e.g. GBP) inside it.
the text in the cell shows 37.00 GBP
the value in the cell shows 37
yes its for ANY currency code, thanks
Therefore it must be format, so how do you use format to have other currency codes. I can see how to add one, or maybe two if it were value dependent.
You would need VBA to check if the numberformat contained GBP or the other possibilities. Doi you have a list of those on the spreadsheet?
What are you trying to do, there may be a better way.
You can send the cell to the VarType function in vb, and currency is vartype 6. It doesn't recognize the GBP type cells, but there is a list in the Format Cells window, under the "Symbol" listbox for the Currency type. You may have to check for those in the cell.text using regex, and see if the cell.value is numeric.
[vba]If VarType(Range(sFareRange).Text) Then
Range(sFareRange).Interior.ColorIndex = iColour
Exit For
End If
[/vba]using varType here, but it doesnt seem to work?
Last edited by mvidas; 12-19-2005 at 06:12 AM. Reason: changing [code] and [/code] to [vba] and [/vba] to fix spacing issues
VarType is definately less than ideal, but the way you'd use it like this is:[vba] If VarType(Range(sFareRange).Text) = 6 Then[/vba]Though I think you'd be better off making a user defined function (only define what can be currency for you), like[vba]Function IsCurrency(ByVal TheCell As Range) As Boolean
If VarType(TheCell) = 6 Then '6 = vbCurrency
IsCurrency = True
Exit Function
End If
If IsNumeric(TheCell.Value) Then
If Left(TheCell.Text, 1) = "$" Or Left(TheCell.Text, 1) = "?" Or _
InStr(1, TheCell.Text, "GBP") > 0 Then
IsCurrency = True
Exit Function
End If
End If
End Function[/vba]That way all you'd have to do is something like[vba]If IsCurrency(Range(sFareRange)) = 6 Then[/vba]Matt
Posting Permissions
Reply With Quote
