Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA Case-Sensitive check

Posted on 2009-02-23
11
Medium Priority
?
892 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:JC_Lives
  • 6
  • 5
11 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 23713870
If (LOWER(TRIM(range("'all defects'!A1").Value)) <> "defecttype" Or LOWER(TRIM(range("'all defects'!B1").Value ))<> "defectid") Then
0
 

Author Comment

by:JC_Lives
ID: 23714052
that looks like it will work perfectly, but now I get a compile error: sub or fuction not defined...
0
 
LVL 33

Expert Comment

by:jppinto
ID: 23714065
The code that you've provided it's part of a sub or function?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:JC_Lives
ID: 23714151
yes, part of a sub
0
 
LVL 33

Expert Comment

by:jppinto
ID: 23714165
Check if the number of ) is right...
0
 

Author Comment

by:JC_Lives
ID: 23714201
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
 
LVL 33

Expert Comment

by:jppinto
ID: 23714217
If (LOWER(TRIM(range("'all defects'!A1").Value))) <> "defecttype" Or LOWER(TRIM(range("'all defects'!B1").Value )))<> "defectid" Then
0
 

Author Comment

by:JC_Lives
ID: 23714393
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
 

Author Comment

by:JC_Lives
ID: 23714986
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
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 23715083
Sorry, I made the code in Excel and in Excel the function is Lower, but in VBA is Lcase! Sorry! :)
0
 

Author Comment

by:JC_Lives
ID: 23715322
no worries :) thx for the help!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question