 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2007Visual Basic Question
I have used some formulas in Visual Basic which have worked very well, but I have come across a small, but still irritating problem. I have a column M which has different statuses (Offer Accepted, Offer Declined, Offer Outstanding, 1st Round Offer, 2nd Round Offer, Assessment Centre), I created the following formula in Visual Basic TryD: If Intersect(Target, Range("$M$10:$M$209")) Is Nothing Then GoTo TryE For Each myCell In Intersect(Target, Range("$M$10:$M$209")) If myCell.Value <> "Offer" Then Application.EnableEvents = False myCell.Offset(0, 1).ClearContents Application.EnableEvents = True End If Next myCell What happens is if I change the status from Offer Declined to Offer Accepted in column M, it removes everything in column N, when I only want that to happen when M does not contain "offer" or is blank. Is there anyway of doing this? Hi Alex, Instead of: If myCell.Value <> "Offer" Then try: If InStr(myCell.Value, "Offer") = 0 Then Cheers  Signaturemacropod [MVP - Microsoft Word] | I have used some formulas in Visual Basic which have worked very well, but I | have come across a small, but still irritating problem. [quoted text clipped - 18 lines] | happen when M does not contain "offer" or is blank. | Is there anyway of doing this? Excellent, thanks for that. > Hi Alex, > [quoted text clipped - 27 lines] > | happen when M does not contain "offer" or is blank. > | Is there anyway of doing this? You could also use If Not myCell.Value Like "Offer*" Then SignatureHTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi Alex, > [quoted text clipped - 27 lines] > | happen when M does not contain "offer" or is blank. > | Is there anyway of doing this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.