Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
case sensitive IF, Visual Basic macro IF
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? -- MKL |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
case sensitive IF, Visual Basic macro IF
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" wrote: 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? -- MKL |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
case sensitive IF, Visual Basic macro IF
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 "Mike H" wrote: 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" wrote: 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? -- MKL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif function: how to distinguish case/make case sensitive | Excel Worksheet Functions | |||
Modify A Macro To Recognize Case Sensitive | Excel Worksheet Functions | |||
problem with macro - its case sensitive! - help please | Excel Programming | |||
How to use Macro using Visual Basic | Excel Programming | |||
Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming |