Messy sheet calculation

[Fixador]

Hi All,

I have a messy sheet calculation that needs tidying

Basically I am identifying RACE TYPE , so as an example , say I was looking for Claiming, then I would use in cell R8

Code: Select all

=IF(FINDB("Claim",A8,1),1,0)

Which is fine when A8 contains "Claim" within the race title - but when it doesn't - i get #VALUE!

to get away from the mess , the next column , cell S8 has

Code: Select all

=IF(ISERROR(R8),0,R8)

Oh what a mess ! Any ideas how i could clean this sheet calculation up ???

Since i plan on changing this to vba shortly - could anyone give me a pointer as to how this would be done in vba ?

cheers

[GeorgeUK]

Strange this one.
Tried it with FindB and Search looks like on is case sensitive, but the other not.
=IF(ISNUMBER(FINDB("Claim",A8,1)),1,0)

I think this is what's required for VBA - again, be careful with the case sensitivity. (Claim is not the same as claim)
IF INSTR(CELL(X,Y).VALUE,"Claim")>0 THEN

[Fixador]

Thanks GeorgeUK - sheet calc works fine - i hated the messy #VALUE!

[Mitch]

If it's just the #VALUE! you don't want to see then you could use the ISERROR in conditional formatting to make the text colour white if the cell contains an error.