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

--
macropod
[MVP - Microsoft Word]


"Alex" wrote in message ...
| 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?
|
|

Excellent, thanks for that.

"macropod" wrote:

Hi Alex,

Instead of:
If myCell.Value < "Offer" Then
try:
If InStr(myCell.Value, "Offer") = 0 Then

Cheers

--
macropod
[MVP - Microsoft Word]


"Alex" wrote in message ...
| 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?
|
|

You could also use

If Not myCell.Value Like "Offer*" Then

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"macropod" wrote in message
...
Hi Alex,

Instead of:
If myCell.Value < "Offer" Then
try:
If InStr(myCell.Value, "Offer") = 0 Then

Cheers

--
macropod
[MVP - Microsoft Word]


"Alex" wrote in message
...
| 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?
|
|