|
Adding a Tab in a Macro
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_He End Sub What should I be typing different to make the macro tab over from the found cell? thanks |
Adding a Tab in a Macro
Activecell.Offset(0,1).Select will move one cell to the right of the current
cell " wrote: 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_He End Sub What should I be typing different to make the macro tab over from the found cell? thanks |
Adding a Tab in a Macro
Selection.offset(0,-1).Select < would send it 1 to the left
Selection.offset(0,1).Select < would send it 1 to the right wrote in message oups.com... 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_He End Sub What should I be typing different to make the macro tab over from the found cell? thanks |
Adding a Tab in a Macro
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. |
Adding a Tab in a Macro
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? |
Adding a Tab in a Macro
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? |
Adding a Tab in a Macro
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 michaelberrier wrote: 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 |
Adding a Tab in a Macro
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" wrote in message ... 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 michaelberrier wrote: 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 |
Adding a Tab in a Macro
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 wrote: 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" wrote in message ... 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 michaelberrier wrote: 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 -- Dave Peterson |
Adding a Tab in a Macro
Dave:
Thanks for the clarification. I'm now better informed. Jim "Dave Peterson" wrote: 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 wrote: 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" wrote in message ... 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 michaelberrier wrote: 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 -- Dave Peterson |
| All times are GMT +1. The time now is 09:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com