Adding a Tab in a Macro

[michaelberrier]

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 "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:=vbTab


GoTo End_Here
End_Here:

End Sub

What should I be typing different to make the macro tab over from the
found cell?
thanks

 

[Dave Peterson]

Your'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 & " wasn't found"
Else
FoundCell.Offset(0, 1).Select
End If
End Sub

 

[bigwheel]

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

 

[Jim May]

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

 

[michaelberrier]

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.
(e-mail address removed)

Thanks.

 

[michaelberrier]

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("$b$7")) 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?

 

[michaelberrier]

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("$b$7")) 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?

 

[Dave Peterson]

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 > 1 then exit sub 'only one cell at a time

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

if isempty(target.value) then exit sub

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

End Sub

 

[Jim May]

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("a7:I7").Select
Selection.Cut
ActiveWindow.ScrollRow = 561
ActiveWindow.SmallScroll Down:=398
Range("a990").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("b7")) 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

 

[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("a7:i7").select line, then step through,
you'll see it go through the procedure twice.

 

[Jim May]

Dave:
Thanks for the clarification. I'm now better informed.
Jim