Excel VBA Case-Sensitive check

Could you help me compare the contents of a cell with a string, trim the cell contents to ignore spaces and also ignore the case?? The following code works.. how would you modify it?? Thanks

If (range("'all defects'!A1").Value <> "Defect Type" Or _
        range("'all defects'!B1").Value <> "Defect ID") Then
        MsgBox "The input for 'all defects' was not expected. Please refer to the 'sample input' sheet and ensure each column is labeled as usual."
        Exit Sub
    End If

Open in new window

JC_LivesAsked:
Who is Participating?
 
jppintoCommented:
Sorry, I made the code in Excel and in Excel the function is Lower, but in VBA is Lcase! Sorry! :)
0
 
jppintoCommented:
If (LOWER(TRIM(range("'all defects'!A1").Value)) <> "defecttype" Or LOWER(TRIM(range("'all defects'!B1").Value ))<> "defectid") Then
0
 
JC_LivesAuthor Commented:
that looks like it will work perfectly, but now I get a compile error: sub or fuction not defined...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
jppintoCommented:
The code that you've provided it's part of a sub or function?
0
 
JC_LivesAuthor Commented:
yes, part of a sub
0
 
jppintoCommented:
Check if the number of ) is right...
0
 
JC_LivesAuthor Commented:
Now I have,

If (LOWER(Trim(range("'all defects'!A1").Value)) <> "defecttype" Or _
        LOWER(Trim(range("'all defects'!B1").Value)) <> "defectid") Then

which returns "sub or function not defined" in excel vba 2003...
0
 
jppintoCommented:
If (LOWER(TRIM(range("'all defects'!A1").Value))) <> "defecttype" Or LOWER(TRIM(range("'all defects'!B1").Value )))<> "defectid" Then
0
 
JC_LivesAuthor Commented:
I copied your code and just added a ")" before the "Then", but the compiler gives an error; I think possibly we had the right amount of brackets in your first recommendation.  

Also when the code fails based on your first recommendation, it highlights the word "LOWER".  

Thanks for your help btw
0
 
JC_LivesAuthor Commented:
I gues it was with the function lower... this code worked:
LCase(Trim(range("'all defects'!A1").Value)) <> "defect type") Then...

Thanks for the solution though, it worked perfectly except for lower doesn't seem to be recognized by my version of excel...
0
 
JC_LivesAuthor Commented:
no worries :) thx for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.