View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Norman Jones Norman Jones is offline
external usenet poster
 
Posts: 5,302
Default IF statement that looks at part of the contents of a cell.

Hi Jon,

'=============
Public Sub TestIt()
Dim Rng As Range
Dim rcell As Range

Set rcell = Range("A1") '<<==== CHANGE

If FirstAlpha(rcell.Value) = "P" Then
Range("G5").Value = 2.5
ElseIf FirstAlpha(rcell.Value) = "Y" Then
Range("G5").Value = 3
Else
'do something else?
End If
End Sub

'-----------------

Public Function FirstAlpha(sStr As String)
Dim Re As Object

Set Re = CreateObject("VBScript.RegExp")
Re.IgnoreCase = True
Re.Global = True
Re.Pattern = "\d"

FirstAlpha = Left(Re.Replace(sStr, vbNullString), 1)

End Function
'<<=============


---
Regards,
Norman



"Jon" wrote in message
oups.com...
I have a need to make decisions based on the content of a cell that
contains an alphanumeric string such as P8072A. I want the ability to
look at this cell and take an action based on what the first letter in
the string is. For example, If the string is P8072A, I want cell G5 to
be 2.5, and if the string is Y2898B then I want cell G5 to be 3.0
(because the first letter changed from P to Y). Is there a way to have
excel look at letters in a cell to make logical decisions?