Solved: Check cell for text

[MrAshton]

I know how to make an If statement for a case where a cell = a certain value but how can I set up an if statemement depending on looking inside the cell for partial text.

I have 4 possible headers that change at the users Whim. I need to be able to read what's in there to decide what to do with the values below the headers.
Example Headers: "Min Transmitance" , "Max Transmitance" , "Average Transmitance"

THe problem is that it might not always be worded that way, so I only want to see if the cell contains the word Min, Max, Average... etc. How do I do this?

[lenze]

Maybe something like this

=IF(ISNUMBER(SEARCH("Min",G2)),TRUE,FALSE)

lenze

[MrAshton]

I need this code to be in VBA sintax Lenze. I'm going to run a string comparison with using * before and after the values.. such as

for each rngcount in masterH
if rngcount = empty then
end if
elseif rngcount = *min* then...
elseif rngcount = *max* then ....

I don't think it will work exactly as written.. but I think the concept is there.. just not syntax

[Erdin? E. Ka]

MrAshton,
Check this code sample;


Sample Values:
A1: Min Transmitance
B1: Erdin? E. Kara?am
C1: Max Transmitance
D1: MrAshton
E1: Average Transmitance


Sub MrAshton()
Dim CLL As Range
For Each CLL In Range("A1:E1")
If UBound(Split(CLL, "Max")) > 0 _
Or UBound(Split(CLL, "Min")) > 0 _
Or UBound(Split(CLL, "Average")) > 0 Then
MsgBox CLL.Address(0, 0)
End If
Next
End Sub

Returning Result is:
A1
C1
E1

[Bob Phillips]

For Each rngcount In masterH

If Instr(rngcount, "min") > then...

ElseIf Instr(rngcount, "max") > 0 Then ...

...
End If
Next rngcount

[MrAshton]

Awesome, I was able to use this
If UBound(Split(rngcount, "Average")) > 0 then
<my formula goes here>
elseif UBound(Split(rngcount, "Max")) > 0 then
<other formula goes here>
elseif UBound(Split(rngcount, "Min")) > 0 then
<yet another formula here>

I've never seen UBound before, Thanks Er

[MrAshton]

Oh it would appear that either method works. Good ol VBA and it's many ways of accomplishing the same thing. Thanks Guys!

[Erdin? E. Ka]

Not at all MrArston,
Also Bob's solution is awesome too.

By the way, i agree your comments about VBA.

Take care.