software

I'm running a simple macro to find and go to a cell containing specific
data using the cell.find method.
I would like to add a Tab over to the cell adjacent to the found cell,
and have tried the following:

selection.typetext text:=vbTab

but VB retruns an error saying quot;Object doesn't support this property or
method.

Here's the whole code:
Sub Look_Here()
Dim WhatFor
WhatFor = ActiveSheet.Cells(7, 2)

Cells.Find(What:=WhatFor, after:=ActiveCell, SearchDirection:=xlNext,
searchorder:=xlByRows, MatchCase:=False).Activate
Selection.TypeText Text:=vbTabGoTo End_Here
End_He

End Sub

What should I be typing different to make the macro tab over from the
found cell?
thanksYour'e trying to select the cell to the right?

Option Explicit
Sub Look_Here()
Dim FoundCell As Range
Dim WhatFor As Variant
WhatFor = ActiveSheet.Cells(7, 2).Value

Set FoundCell = Cells.Find(What:=WhatFor, after:=ActiveCell, _
SearchDirection:=xlNext, searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox WhatFor amp; quot; wasn't foundquot;
Else
FoundCell.Offset(0, 1).Select
End If
End Sub

wrote:
gt;
gt; I'm running a simple macro to find and go to a cell containing specific
gt; data using the cell.find method.
gt; I would like to add a Tab over to the cell adjacent to the found cell,
gt; and have tried the following:
gt;
gt; selection.typetext text:=vbTab
gt;
gt; but VB retruns an error saying quot;Object doesn't support this property or
gt; method.
gt;
gt; Here's the whole code:
gt; Sub Look_Here()
gt; Dim WhatFor
gt; WhatFor = ActiveSheet.Cells(7, 2)
gt;
gt; Cells.Find(What:=WhatFor, after:=ActiveCell, SearchDirection:=xlNext,
gt; searchorder:=xlByRows, MatchCase:=False).Activate
gt; Selection.TypeText Text:=vbTab
gt;
gt; GoTo End_Here
gt; End_He
gt;
gt; End Sub
gt;
gt; What should I be typing different to make the macro tab over from the
gt; found cell?
gt; thanks

--

Dave Peterson

Activecell.Offset(0,1).Select will move one cell to the right of the current
cell

quot; wrote:

gt; I'm running a simple macro to find and go to a cell containing specific
gt; data using the cell.find method.
gt; I would like to add a Tab over to the cell adjacent to the found cell,
gt; and have tried the following:
gt;
gt; selection.typetext text:=vbTab
gt;
gt; but VB retruns an error saying quot;Object doesn't support this property or
gt; method.
gt;
gt; Here's the whole code:
gt; Sub Look_Here()
gt; Dim WhatFor
gt; WhatFor = ActiveSheet.Cells(7, 2)
gt;
gt; Cells.Find(What:=WhatFor, after:=ActiveCell, SearchDirection:=xlNext,
gt; searchorder:=xlByRows, MatchCase:=False).Activate
gt; Selection.TypeText Text:=vbTab
gt;
gt;
gt; GoTo End_Here
gt; End_He
gt;
gt; End Sub
gt;
gt; What should I be typing different to make the macro tab over from the
gt; found cell?
gt; thanks
gt;
gt;

Selection.offset(0,-1).Select lt; would send it 1 to the left
Selection.offset(0,1).Select lt; would send it 1 to the right

gt; wrote in message oups.com...
gt; I'm running a simple macro to find and go to a cell containing specific
gt; data using the cell.find method.
gt; I would like to add a Tab over to the cell adjacent to the found cell,
gt; and have tried the following:
gt;
gt; selection.typetext text:=vbTab
gt;
gt; but VB retruns an error saying quot;Object doesn't support this property or
gt; method.
gt;
gt; Here's the whole code:
gt; Sub Look_Here()
gt; Dim WhatFor
gt; WhatFor = ActiveSheet.Cells(7, 2)
gt;
gt; Cells.Find(What:=WhatFor, after:=ActiveCell, SearchDirection:=xlNext,
gt; searchorder:=xlByRows, MatchCase:=False).Activate
gt; Selection.TypeText Text:=vbTab
gt;
gt;
gt; GoTo End_Here
gt; End_He
gt;
gt; End Sub
gt;
gt; What should I be typing different to make the macro tab over from the
gt; found cell?
gt; thanks
gt;
Thanks for all the help. I need some more advanced help with the
workbook this macro function inside. Anyone willing to devote 10
minutes to answer a simple question or 10 please email me.Thanks.I've stepped through my code using F8 and have found an instance where
the code directs an Exit Sub if a certain cell is blank, but it is
continuing...here is the code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range(quot;$b$7quot;)) Is Nothing Then Exit Sub
'exit unless it Is c1 that changed

Call Module22.Look_Here1
'the macro To Call when c1 changes

End Sub

Cell B7 is empty, no values, no formulas, nothing, but the macro
proceeds to the next step and that's where it goes off kilter.

Any idea why it's doing that?I've stepped through my code using F8 and have found an instance where
the code directs an Exit Sub if a certain cell is blank, but it is
continuing...here is the code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range(quot;$b$7quot;)) Is Nothing Then Exit Sub
'exit unless it Is c1 that changed

Call Module22.Look_Here1
'the macro To Call when c1 changes

End Sub

Cell B7 is empty, no values, no formulas, nothing, but the macro
proceeds to the next step and that's where it goes off kilter.

Any idea why it's doing that?That's not checking to see if B7 is empty (or looks blank). It's just checking
to see if the cell you changed was B7.

I like this style:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

if target.cells.count gt; 1 then exit sub 'only one cell at a time

'exit unless it Is B7 that changed
If Intersect(Target, me.Range(quot;$b$7quot;)) Is Nothing Then Exit Sub

if isempty(target.value) then exit sub

Call Module22.Look_Here1
'the macro To Call when b7 changes

End Submichaelberrier wrote:
gt;
gt; I've stepped through my code using F8 and have found an instance where
gt; the code directs an Exit Sub if a certain cell is blank, but it is
gt; continuing...here is the code.
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt;
gt; If Intersect(Target, Range(quot;$b$7quot;)) Is Nothing Then Exit Sub
gt; 'exit unless it Is c1 that changed
gt;
gt; Call Module22.Look_Here1
gt; 'the macro To Call when c1 changes
gt;
gt; End Sub
gt;
gt; Cell B7 is empty, no values, no formulas, nothing, but the macro
gt; proceeds to the next step and that's where it goes off kilter.
gt;
gt; Any idea why it's doing that?

--

Dave Peterson

Dave: I was trying to help Michael out (privately) and had a few
questions. The Change event you are speaking to here is triggered as
follows (by first running AddNAME()

Sub ADDNAME() ' Macro (Only first 6 lines - like so):

Range(quot;a7:I7quot;).Select
Selection.Cut
ActiveWindow.ScrollRow = 561
ActiveWindow.SmallScroll Down:=398
Range(quot;a990quot;).Select
ActiveSheet.Paste

As a result of the 1st 6 lines of code (above) 2 or 3 things are happening,
which I do not understand..
1) Line 6 ActiveShet.Paste is triggering a Worksheet_Change Event. WHY????
2) The resulting WS-Change Event starts with statement:
If Intersect(Target, Range(quot;b7quot;)) Is Nothing Then Exit Sub
(At this point - In the immedaite window when I do ? Target.address ' I
get A7:I7 (??)
And after processing this line it (interpreting it as False) it proceeds to
the next line
where it Calls Look-Here1 (a standard module)..

Can you elaborate of a few of these points,
TIA,
Jim

quot;Dave Petersonquot; gt; wrote in message
...
gt; That's not checking to see if B7 is empty (or looks blank). It's just
gt; checking
gt; to see if the cell you changed was B7.
gt;
gt; I like this style:
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt;
gt; if target.cells.count gt; 1 then exit sub 'only one cell at a time
gt;
gt; 'exit unless it Is B7 that changed
gt; If Intersect(Target, me.Range(quot;$b$7quot;)) Is Nothing Then Exit Sub
gt;
gt; if isempty(target.value) then exit sub
gt;
gt; Call Module22.Look_Here1
gt; 'the macro To Call when b7 changes
gt;
gt; End Sub
gt;
gt;
gt; michaelberrier wrote:
gt;gt;
gt;gt; I've stepped through my code using F8 and have found an instance where
gt;gt; the code directs an Exit Sub if a certain cell is blank, but it is
gt;gt; continuing...here is the code.
gt;gt;
gt;gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt;gt;
gt;gt; If Intersect(Target, Range(quot;$b$7quot;)) Is Nothing Then Exit Sub
gt;gt; 'exit unless it Is c1 that changed
gt;gt;
gt;gt; Call Module22.Look_Here1
gt;gt; 'the macro To Call when c1 changes
gt;gt;
gt;gt; End Sub
gt;gt;
gt;gt; Cell B7 is empty, no values, no formulas, nothing, but the macro
gt;gt; proceeds to the next step and that's where it goes off kilter.
gt;gt;
gt;gt; Any idea why it's doing that?
gt;
gt; --
gt;
gt; Dave Peterson
Pasting to a sheet is a change to that sheet--so the event fires.

If you want to stop the event from firing, you can turn off the event monitoring
and do the work.

application.enableevents = false
'do some stuff
application.enableevents = true

And cutting something from the sheet and pasting it elsewhere is seen as two
changes--one for the cut and one for the paste.

So I'm thinking that you're seeing the first pass (cutting A7:I7) drop to the
next line (B7 is part of that range)--so it doesn't exit the sub.

If you add a breakpoint on the Range(quot;a7:i7quot;).select line, then step through,
you'll see it go through the procedure twice.

Jim May wrote:
gt;
gt; Dave: I was trying to help Michael out (privately) and had a few
gt; questions. The Change event you are speaking to here is triggered as
gt; follows (by first running AddNAME()
gt;
gt; Sub ADDNAME() ' Macro (Only first 6 lines - like so):
gt;
gt; Range(quot;a7:I7quot;).Select
gt; Selection.Cut
gt; ActiveWindow.ScrollRow = 561
gt; ActiveWindow.SmallScroll Down:=398
gt; Range(quot;a990quot;).Select
gt; ActiveSheet.Paste
gt;
gt; As a result of the 1st 6 lines of code (above) 2 or 3 things are happening,
gt; which I do not understand..
gt; 1) Line 6 ActiveShet.Paste is triggering a Worksheet_Change Event. WHY????
gt; 2) The resulting WS-Change Event starts with statement:
gt; If Intersect(Target, Range(quot;b7quot;)) Is Nothing Then Exit Sub
gt; (At this point - In the immedaite window when I do ? Target.address ' I
gt; get A7:I7 (??)
gt; And after processing this line it (interpreting it as False) it proceeds to
gt; the next line
gt; where it Calls Look-Here1 (a standard module)..
gt;
gt; Can you elaborate of a few of these points,
gt; TIA,
gt; Jim
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; That's not checking to see if B7 is empty (or looks blank). It's just
gt; gt; checking
gt; gt; to see if the cell you changed was B7.
gt; gt;
gt; gt; I like this style:
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt;
gt; gt; if target.cells.count gt; 1 then exit sub 'only one cell at a time
gt; gt;
gt; gt; 'exit unless it Is B7 that changed
gt; gt; If Intersect(Target, me.Range(quot;$b$7quot;)) Is Nothing Then Exit Sub
gt; gt;
gt; gt; if isempty(target.value) then exit sub
gt; gt;
gt; gt; Call Module22.Look_Here1
gt; gt; 'the macro To Call when b7 changes
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; michaelberrier wrote:
gt; gt;gt;
gt; gt;gt; I've stepped through my code using F8 and have found an instance where
gt; gt;gt; the code directs an Exit Sub if a certain cell is blank, but it is
gt; gt;gt; continuing...here is the code.
gt; gt;gt;
gt; gt;gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; gt;gt;
gt; gt;gt; If Intersect(Target, Range(quot;$b$7quot;)) Is Nothing Then Exit Sub
gt; gt;gt; 'exit unless it Is c1 that changed
gt; gt;gt;
gt; gt;gt; Call Module22.Look_Here1
gt; gt;gt; 'the macro To Call when c1 changes
gt; gt;gt;
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt; Cell B7 is empty, no values, no formulas, nothing, but the macro
gt; gt;gt; proceeds to the next step and that's where it goes off kilter.
gt; gt;gt;
gt; gt;gt; Any idea why it's doing that?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson

--

Dave Peterson