case sensitive IF, Visual Basic macro IF

[Rock Doc]

I want to know how to do something in both Excel and in a Visual Basic macro.

I have cells in a worksheet range, each with one character. The possible
characters are the letters of the alphabet, both upper and lower case, so one
of 52 possibilities in any one cell.

Ideally, I would like to use an IF statement. For example,
=IF(E5="b",TRUE,FALSE).
However, I need case sensitivity.

In Excel, one possibility might be to use the code() function. For example,
=IF(code(E5)=98,TRUE,FALSE), however, I have not tried this to see if it
works.

My real interest is in doing the same thing in a macro. If the macro were
case sensitive, I could use the statement

If Worksheets("Sheet1").Cells(Mrow, MCol) = "b" Then
...
End If
Here, Mrow and MCol are variables of two loops operating on a row range and
a column range, respectively.

Is there a way to make the Visual Basic If function case sensitive.

Alternatively, I tried the following, trying to take advantage of the
evaluate function:

xxx = [Worksheets{"Sheet1").Cells(5,5)]
Worksheets("Sheet1").Cells(1, 1) = xxx

What was placed in the row 1, column 1, was #Value, so I don't think this
would work. My hope was that the evaluate function would set the cell
contents to the ASCII code for "b" (98). I could then modify the IF
statement to change "b" to its ASCII code.

Obviously, I am not understanding something correctly. Can anyone out there
assist a relative beginner?

 

[Mike H]

Hi,

The statement

If Worksheets("Sheet1").Cells(Mrow, MCol) = "b" Then
...
End If

is case sensitive and will only return true for a lower case b


To get case sensditiivity on the worksheet you van use EXACT

=IF(EXACT(E5,"b"),TRUE,FALSE)

Mike

 

[Rock Doc]

Thanks, Mike.

I should have designed a test to verify that there was a case sensitivity
problem in Visual Basic, instead of assuming that the problem in Excel also
translated to VB.
--
MKL

Hi,

The statement

If Worksheets("Sheet1").Cells(Mrow, MCol) = "b" Then
...
End If

is case sensitive and will only return true for a lower case b


To get case sensditiivity on the worksheet you van use EXACT

=IF(EXACT(E5,"b"),TRUE,FALSE)

Mike

I want to know how to do something in both Excel and in a Visual Basic macro.

I have cells in a worksheet range, each with one character. The possible
characters are the letters of the alphabet, both upper and lower case, so one
of 52 possibilities in any one cell.

Ideally, I would like to use an IF statement. For example,
=IF(E5="b",TRUE,FALSE).
However, I need case sensitivity.

In Excel, one possibility might be to use the code() function. For example,
=IF(code(E5)=98,TRUE,FALSE), however, I have not tried this to see if it
works.

My real interest is in doing the same thing in a macro. If the macro were
case sensitive, I could use the statement

If Worksheets("Sheet1").Cells(Mrow, MCol) = "b" Then
...
End If
Here, Mrow and MCol are variables of two loops operating on a row range and
a column range, respectively.

Is there a way to make the Visual Basic If function case sensitive.

Alternatively, I tried the following, trying to take advantage of the
evaluate function:

xxx = [Worksheets{"Sheet1").Cells(5,5)]
Worksheets("Sheet1").Cells(1, 1) = xxx

What was placed in the row 1, column 1, was #Value, so I don't think this
would work. My hope was that the evaluate function would set the cell
contents to the ASCII code for "b" (98). I could then modify the IF
statement to change "b" to its ASCII code.

Obviously, I am not understanding something correctly. Can anyone out there
assist a relative beginner?