if cell contains this AND that - like operator?

  • Thread starter Thread starter MiataDiablo
  • Start date Start date
M

MiataDiablo

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;
Click to expand...

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
'<<==========
 
Hi MiataDiablo,

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


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







- Show quoted text -
Click to expand...

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
 
Back
Top