I've received wonderful help in getting this far in my macro; but how
do I modify the following to make allowance for this AND that? If my
cell isn't empty, I want "Subfile" entered in the next cell; however
if the cell also contains a comma and/or an ampersand, I want the word
"Subfiles" to be entered (i.e."CLN1237, BNK1535 & GEA1981" or "CLN1237
& BNK1535"). Do I insert the "like" operator as I would in an Access
query? If so, how? I tried "*,*" without luck.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("N:N")
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
Else
If v = "," Or v = "&" Then
t.Offset(0, 1).Value = "Subfiles"
Else
t.Offset(0, 1).Value = "Subfile"
End If
End If
Application.EnableEvents = True
End Sub

Hi MiataDiablo,

You should not open a new thread for
an on-going topic, especially in view of
your unsolicted testimonial:

I've received wonderful help in getting this far in my macro;

This is because it is inefficient in that it
causes an unnecessary duplication of the
time and effort of those who seek to assist
you; it is discourteous to those who may
have already assisted you and, finally, it
substantially reduces the prospects of an
optimal response in that one respondent
may well not see the suggestions of another.


That said, try the following adaptation of
your existing code:

'==========
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range
Dim r As Range
Dim v As Variant
Const sStr As String = "SubFile"
Const sStr2 As String = "SubFiles"

Set t = Target
Set r = Range("N:N")

If Intersect(t, r) Is Nothing Then
Exit Sub
End If

On Error GoTo XIT
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
Else
If InStr(1, v, ",", vbTextCompare) 0 _
Or InStr(1, v, "&", vbTextCompare) 0 Then
t.Offset(0, 1).Value = sStr2
Else
t.Offset(0, 1).Value = sStr
End If
End If

XIT:
Application.EnableEvents = True
End Sub
'<<==========



---
Regards.
Norman

wrote in message
...
I've received wonderful help in getting this far in my macro; but how
do I modify the following to make allowance for this AND that? If my
cell isn't empty, I want "Subfile" entered in the next cell; however
if the cell also contains a comma and/or an ampersand, I want the word
"Subfiles" to be entered (i.e."CLN1237, BNK1535 & GEA1981" or "CLN1237
& BNK1535"). Do I insert the "like" operator as I would in an Access
query? If so, how? I tried "*,*" without luck.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("N:N")
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
Else
If v = "," Or v = "&" Then
t.Offset(0, 1).Value = "Subfiles"
Else
t.Offset(0, 1).Value = "Subfile"
End If
End If
Application.EnableEvents = True
End Sub

On May 5, 2:09*am, "Norman Jones"
wrote:
Hi MiataDiablo,

You should *not open a new thread for
an on-going topic, especially in view of
your unsolicted testimonial:

I've received wonderful help in getting this far in my macro;

This is because it is inefficient in that it
causes an unnecessary duplication of the
time and effort of those who seek to assist
you; it is discourteous to those who may
have already assisted you and, finally, it
substantially reduces the prospects of an
optimal response in that one respondent
may well not see the suggestions of another.

That said, try the following adaptation of
your existing code:

'==========
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
* * Dim t As Range
* * Dim r As Range
* * Dim v As Variant
* * Const sStr As String = "SubFile"
* * Const sStr2 As String = "SubFiles"

* * Set t = Target
* * Set r = Range("N:N")

* * If Intersect(t, r) Is Nothing Then
* * * * Exit Sub
* * End If

* * On Error GoTo XIT
* * Application.EnableEvents = False
* * v = t.Value
* * If v = "" Then
* * * * t.Offset(0, 1).Value = ""
* * Else
* * * * If InStr(1, v, ",", vbTextCompare) 0 _
* * * * * * * * * *Or InStr(1, v, "&", vbTextCompare) 0 Then
* * * * * * t.Offset(0, 1).Value = sStr2
* * * * Else
* * * * * * t.Offset(0, 1).Value = sStr
* * * * End If
* * End If

XIT:
* * Application.EnableEvents = True
End Sub
'<<==========

---
Regards.
Norman

wrote in message

...



I've received wonderful help in getting this far in my macro; but how
do I modify the following to make allowance for this AND that? *If my
cell isn't empty, I want "Subfile" entered in the next cell; however
if the cell also contains a comma and/or an ampersand, I want the word
"Subfiles" to be entered (i.e."CLN1237, BNK1535 & GEA1981" or "CLN1237
& BNK1535"). Do I insert the "like" operator as I would in an Access
query? *If so, how? *I tried "*,*" without luck.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("N:N")
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
Else
If v = "," Or v = "&" Then
t.Offset(0, 1).Value = "Subfiles"
Else
t.Offset(0, 1).Value = "Subfile"
End If
End If
Application.EnableEvents = True
End Sub- Hide quoted text -

- Show quoted text -

I am extremely apologetic for starting a new thread, I do know better
and haven't done so before, but (isn't there always a but?) I was
desperate to have this resolved before a 9:00 meeting this morning. I
meant absolutely no disrepect to Gary's Student, as he/she has been so
helpful; but it is the weekend and he/she may actually have a life to
attend to and not my Excel dilemna. I didn't know how to send out an
SOS from that thread to garner some attention to anyone else out
there.

Thanks, Norman, this works perfectly! Joy, joy, joy! Gary's Student:
please accept my apologies, I wasn't dissing you, I think youdaone.

No offence taken. If I don't respond in a timely fashion, it means I became
really busy or (more likely) I ran out of good ideas
--
Gary''s Student - gsnu200787


" wrote:

On May 5, 2:09 am, "Norman Jones"
wrote:
Hi MiataDiablo,

You should not open a new thread for
an on-going topic, especially in view of
your unsolicted testimonial:

I've received wonderful help in getting this far in my macro;

This is because it is inefficient in that it
causes an unnecessary duplication of the
time and effort of those who seek to assist
you; it is discourteous to those who may
have already assisted you and, finally, it
substantially reduces the prospects of an
optimal response in that one respondent
may well not see the suggestions of another.

That said, try the following adaptation of
your existing code:

'==========
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range
Dim r As Range
Dim v As Variant
Const sStr As String = "SubFile"
Const sStr2 As String = "SubFiles"

Set t = Target
Set r = Range("N:N")

If Intersect(t, r) Is Nothing Then
Exit Sub
End If

On Error GoTo XIT
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
Else
If InStr(1, v, ",", vbTextCompare) 0 _
Or InStr(1, v, "&", vbTextCompare) 0 Then
t.Offset(0, 1).Value = sStr2
Else
t.Offset(0, 1).Value = sStr
End If
End If

XIT:
Application.EnableEvents = True
End Sub
'<<==========

---
Regards.
Norman

wrote in message

...



I've received wonderful help in getting this far in my macro; but how
do I modify the following to make allowance for this AND that? If my
cell isn't empty, I want "Subfile" entered in the next cell; however
if the cell also contains a comma and/or an ampersand, I want the word
"Subfiles" to be entered (i.e."CLN1237, BNK1535 & GEA1981" or "CLN1237
& BNK1535"). Do I insert the "like" operator as I would in an Access
query? If so, how? I tried "*,*" without luck.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("N:N")
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
Else
If v = "," Or v = "&" Then
t.Offset(0, 1).Value = "Subfiles"
Else
t.Offset(0, 1).Value = "Subfile"
End If
End If
Application.EnableEvents = True
End Sub- Hide quoted text -

- Show quoted text -

I am extremely apologetic for starting a new thread, I do know better
and haven't done so before, but (isn't there always a but?) I was
desperate to have this resolved before a 9:00 meeting this morning. I
meant absolutely no disrepect to Gary's Student, as he/she has been so
helpful; but it is the weekend and he/she may actually have a life to
attend to and not my Excel dilemna. I didn't know how to send out an
SOS from that thread to garner some attention to anyone else out
there.

Thanks, Norman, this works perfectly! Joy, joy, joy! Gary's Student:
please accept my apologies, I wasn't dissing you, I think youdaone.