On May 3, 1:52*pm, MiataDia...@gmail.com wrote:
> On May 3, 8:39*am, Gary''s Student
>
>
>
>
>
> <GarysStud...@discussions.microsoft.com> wrote:
> > Try this:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Set t = Target
> > Set r = Range("N:N")
> >IfIntersect(t, r) Is Nothing Then Exit Sub
> > Application.EnableEvents = False
> > v = t.Value
> >Ifv = "" Then
> > * * t.Offset(0, 1).Value = ""
> > Else
> > * *Ifv = "," Or v = "&" Then
> > * * * * t.Offset(0, 1).Value = "Subfiles"
> > * * Else
> > * * * * t.Offset(0, 1).Value = "Subfile"
> > * * EndIf
> > EndIf
> > Application.EnableEvents = True
> > End Sub
>
> > --
> > Gary''s Student - gsnu2007h
>
> > "MiataDia...@gmail.com" wrote:
> > > On May 3, 6:41 am, Per Jessen <perjesse...@hotmail.com> wrote:
> > > > On 3 Maj, 11:52, MiataDia...@gmail.com wrote:
>
> > > > > dumbest question that will be posted all day, but here goes anyway..
> > > > > How do I change the following macro so that it only applies to one
> > > > > column instead of two? *I don't know how to properly modify
> > > > > Range(Cells(Target.Row, 15), Cells(Target.Row, 16)) to just one of
> > > > > those columns. *Deleting the second "range" gives me a run-time
> > > > > error. *Maybe I should be using something all together different
> > > > > anyway. *When the user enters a value into Column 14, I want theword
> > > > > "Subfile" to appear in Column 15; however,ifthe user deletes the
> > > > > text in Column 14, I want "Subfile" removed/deleted.
>
> > > > > Any and all help is always appreciated.
>
> > > > > Sub Worksheet_Change(ByVal Target As Range)
> > > > > Application.EnableEvents = False
>
> > > > >IfTarget.Column = 14 And Target.Value <> "" Then
> > > > > * * *Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value =
> > > > > "Subfile"
> > > > > EndIf
>
> > > > >IfTarget.Column = 14 And Target.Value = "" Then
> > > > > *Range(Cells(Target.Row, 15), Cells(Target.Row, 16)).Value = ""
> > > > > *EndIf
>
> > > > > Application.EnableEvents = True
> > > > > End Sub
>
> > > > Hi
> > > > Try this:
>
> > > > Range(Cells(Target.Row,15)).Value=""
>
> > > > Regards,
> > > > Per- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > fantastic! *what did I ever do before Google??? *One additional
> > > question that I should have asked before. *If14containsacommaor
> > > an ampersand, then subfile should be subfiles.- Hide quoted text -
>
> > - Show quoted text -
>
> Gary's student, you're not only a great person, but you're officially
> my hero today.- Hide quoted text -
>
> - Show quoted text -
Well, I thought I could figure out the last part from here. What you
gave me works perfectly for subfiles if "N" contains ONLY a comma or
ONLY an ampersand. But the data for that cell will contain text like
"CLN1237, BNK1535 & GEA1981" or "CLN1237 & BNK1535". How do I insert
the "like" operator in your code the way I would use it for a query in
Access? I never thought I would ever say that making things happen in
Access is easier than Excel.
Thanks again for your help, I truly appreciate your time and
assistance.
|
Similar Threads
