We have a new series on chandoo.org. – Excel Challenges. From now, every 1-2 months, I will post an interesting Excel Challenge. These are tricky problems for which elegant solutions should be found. To keep the challenges exciting, we will have a small prize for a winner (if more people answer correctly, we pick one randomly)
Excel Challenge #1 – Find Overlaps in Machine Schedule Dates
The inspiration for this challenge came from our forums. This is a question asked by xlfan (I hope that is not a real name 😉 )
My question—
I work in a manufacturing company which makes different sizes of Nuts on many machines. One machine can do only one size of Nuts for a particular period and can be molded for another variant.I manage an excel sheet defining the date ranges of the machine utility for a particular Nut, this allows us to mark our next point for another size.The problem is.. after fixing dates for a process on a machine, I many time loose track of the previous marked dates and issue new dates for the same machine that is usually overlapping and lately found out.
Please help me with a solution ~~~ which does not allow overlapping of dates on a particular machine unless the previous dates allotted be changed.
I use the Table feature of Excel 2007 for this data
Problem Statement:
Download this Excel file. It has an Excel Table, with the following data (snapshot below). You need to write formulas to display Ok or Not Ok in the adjacent column based on the following criteria:
- Display Ok if the scheduling dates for that row do not overlap with remaining scheduling dates for that machine
- Display Not Ok otherwise.
How to post your answers?
- Post your formulas a comment
- Save the file and upload it to skydrive. Then paste the link to that file thru comments.
- Email me your file with solution at chandoo.d @ gmail.com. Please use the subject EC1 – solution to make sure that I notice your email.
If you are reading this in e-mail or RSS Reader, click here to post comments.
Other Rules
- Helper columns are ok
- Avoid VBA if possible
- Your solution should work in Excel 2007 or above.
What is in for me?
Glory! lots of it. But we know glory cannot give glee. So I am going to give a gift to one lucky winner.
One lucky winner gets:
Business Dashboards: A Visual Catalog for Design and Deployment
Last Date for Submission:
The contest part of this challenge ends on 18th March. That is we will pick the winner of book on that date. But you can continue to send solutions thru comments forever.
Some nuts to feed your brain:
- Original question by xlfan and follow-up discussion
- Finding date overlaps using Excel formulas
- Doing range lookups using Excel
- Examples of Excel Array Formulas
Go ahead and post your answers. We are waiting.
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub