String comparison

[Pawan]

Hi

I have following code which is working fine:

If Sheets("Sheet4").Cells(rw, 1).Value Like "*TIC*daca".

Now I have to modify this line a little bit, but am not able to do it.
I want to do two things:

1. The comparison above is case sensitive. I want to make it case
insensitive. However I donot want to use Option Compare Text as this case
insensitive comparison needs to be valid for this particular code only and
not for other comparisons in the module.

2. I want to compare other values too in the same line. Means in addition to
"*TIC*daca", I want to compare "FI*pida" with the same cell. Result should be
true is cell value is either "*TIC*daca" OR "*FI*pida".

How can I achieve this?

Thank You

 

[Stefano]

1) If Ucase(Sheets("Sheet4").Cells(rw, 1).Value) Like "*TIC*DACA" Then

2) If Ucase(Sheets("Sheet4").Cells(rw, 1).Value) Like "*TIC*DACA" Or
Ucase(Sheets("Sheet4").Cells(rw, 1).Value) Like "*FI*pida" Then

Better if you do this first:
Txt = Ucase(Sheets("Sheet4").Cells(rw, 1).Value)
And then you test Txt

 

[Pawan]

Thanks Stefano

I will try this and will get back to you in case of any doubt.

 

[Pawan]

What should be variable type for Txt. The cell value can be anything (number
or string)

 

[Jacob Skaria]

As you are compring two strings use Range.Text instead of .Value as below and
declare the variable as String.

Sheets("Sheet4").Cells(rw, 1).Text


PS: If a variable is to hold any kind of data then declare that as Variant

Dim varTemp as Variant

If this post helps click Yes

 

[Stefano]

If you want to use the Like operator then you work with strings, then Txt
should be a string.

If you use the Like operator with numbers then VBA will convert a number to
strings.
For example 123 Like "12*" = True

If you want to use different operators then you can use a Variant, then use
the TypeName() function to decide what operator to use according to the value
type.

 

[Rick Rothstein]

Txt = Ucase(Sheets("Sheet4").Cells(rw, 1).Value)

What should be variable type for Txt.

Since Txt is being assigned the return value from the UCase function and
since the UCase function is a String function, I would Dim the Txt variable
as String. The Like operator is used to compare text String values, so this
would be a consistent declaration to make as well. However, VB is very
accommodating and if you Dim'med Txt as a Variant, then it would (under most
circumstances) coerce the Variant to the data type needed to complete the
operation it is being used in.