check a cell to see if it has a currency code

i am looking for a function that will check a cell to see if it has a currency code (e.g. GBP) inside it.

http://vbaexpress.com/forum/images/smilies/118.gif

Quote:

---

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.

http://vbaexpress.com/forum/images/smilies/118.gif

---

Any currency code? Is it in the cell or just part of format?

the text in the cell shows 37.00 GBP
the value in the cell shows 37

yes its for ANY currency code, thanks

Quote:

---

Originally Posted by crmpicco

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?

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