Goto code

[Bob]

I am using the following code to Goto the cell entered in cell "D3". It works
if there is a perfect match. If they do not match the code stops on line 3
with an error. What should be added to the code so that it will work when not
found and stay in cell "D3"? (With a pop of box "Number not Found" or
something like that)

The column has numbers like 4512 and 4512-1 and 4512-01 to match.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$3" Then Exit Sub
Columns(2).Find(Target).Select
End Sub

Thank for your help,

Bob

[Dave Peterson]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
If Target.Address <> "$D$3" Then Exit Sub
res = Application.Match(Target.Value, Me.Range("B:B"), 0)
If IsError(res) Then
'stay put
'beep '???
Else
Me.Range("B:B")(res).Select
End If
End Sub


Bob wrote:
>
> I am using the following code to Goto the cell entered in cell "D3". It works
> if there is a perfect match. If they do not match the code stops on line 3
> with an error. What should be added to the code so that it will work when not
> found and stay in cell "D3"? (With a pop of box "Number not Found" or
> something like that)
>
> The column has numbers like 4512 and 4512-1 and 4512-01 to match.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address <> "$D$3" Then Exit Sub
> Columns(2).Find(Target).Select
> End Sub
>
> Thank for your help,
>
> Bob

--

Dave Peterson

[Bob]

Dave, I am new to VB. I copied the code and getting an error with "Option
Explicit" if I remove it from the code the Goto works with a match, it does
not work if there is not a match and moves out of the cell. What may be wrong?

"Dave Peterson" wrote:

> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim res As Variant
> If Target.Address <> "$D$3" Then Exit Sub
> res = Application.Match(Target.Value, Me.Range("B:B"), 0)
> If IsError(res) Then
> 'stay put
> 'beep '???
> Else
> Me.Range("B:B")(res).Select
> End If
> End Sub
>
>
> Bob wrote:
> >
> > I am using the following code to Goto the cell entered in cell "D3". It works
> > if there is a perfect match. If they do not match the code stops on line 3
> > with an error. What should be added to the code so that it will work when not
> > found and stay in cell "D3"? (With a pop of box "Number not Found" or
> > something like that)
> >
> > The column has numbers like 4512 and 4512-1 and 4512-01 to match.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address <> "$D$3" Then Exit Sub
> > Columns(2).Find(Target).Select
> > End Sub
> >
> > Thank for your help,
> >
> > Bob
>
> --
>
> Dave Peterson
>

[Dave Peterson]

First the easy answer...

"Option Explicit" is a directive to the compiler that tells it that you as the
programmer are going to declare your variables. This line goes at the top of
your module. All the subs and functions in that module will have to have all
the variables declared.

Then if you make a typing mistake:

Dim lCtr as long
lCtr = 1Ctr + 1

You'll get an error since a mistyped variable wasn't Dimmed.

Did you see the error in my example:
One of those lCtr's is (ELL-ctr) and the other is (one)-ctr.

Without that "option explicit" the top, excel will just figure you know what
you're doing and meant to do that. But your code (probably) won't run as
intended.

These kinds of typos can be difficult to find. Forcing yourself to declare the
variables will ease this kind of debugging problem.

And a nice thing about declaring variables is you can use VBA's intellisense.
If you do:

dim Wks as worksheet
set wks = worksheets("sheet1")

then later type

Wks.
(note the dot)
You'll see a list of all the possible methods and properties that go with that
variable type (worksheet in my example).

And another nice thing...

If you have a variable like this:

Dim myCounterOfReceiptsPaidOnTime as long

You can type

mycount
and hit ctrl-spacebar.
You'll see a list of all the stuff that starts with those characters and you can
pick the one you want (or it's only this variable, it'll autocomplete for you).

And I think most people who spend anytime writing macros have this option turned
on automatically for all new modules (you'll still have to type it for existing
modules).

Inside the VBE
Tools|Options|Editor Tab|Check "Require Variable Declaration"

It's one of those things that may seem like too much work at the beginning, but
it really makes life easier when you're typing and debugging.


==============

I like to leave the cursor in the cell that I just changed. I use this setting:
Tools|Options|edit|
I leave the "Move selection after enter" unchecked.

I like this for all my editting, though.

If you don't want to change this setting, you can have your code go back to that
D3 cell.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
If Target.Address <> "$D$3" Then Exit Sub
res = Application.Match(Target.Value, Me.Range("B:B"), 0)
If IsError(res) Then
'stay put
'beep '???
Application.EnableEvents = False
Target.Select
Application.EnableEvents = True
Else
Me.Range("B:B")(res).Select
End If
End Sub


Bob wrote:
>
> Dave, I am new to VB. I copied the code and getting an error with "Option
> Explicit" if I remove it from the code the Goto works with a match, it does
> not work if there is not a match and moves out of the cell. What may be wrong?
>
> "Dave Peterson" wrote:
>
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim res As Variant
> > If Target.Address <> "$D$3" Then Exit Sub
> > res = Application.Match(Target.Value, Me.Range("B:B"), 0)
> > If IsError(res) Then
> > 'stay put
> > 'beep '???
> > Else
> > Me.Range("B:B")(res).Select
> > End If
> > End Sub
> >
> >
> > Bob wrote:
> > >
> > > I am using the following code to Goto the cell entered in cell "D3". It works
> > > if there is a perfect match. If they do not match the code stops on line 3
> > > with an error. What should be added to the code so that it will work when not
> > > found and stay in cell "D3"? (With a pop of box "Number not Found" or
> > > something like that)
> > >
> > > The column has numbers like 4512 and 4512-1 and 4512-01 to match.
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Address <> "$D$3" Then Exit Sub
> > > Columns(2).Find(Target).Select
> > > End Sub
> > >
> > > Thank for your help,
> > >
> > > Bob
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Bob]

Works Great
Thanks for your help,
Bob

"Dave Peterson" wrote:

> First the easy answer...
>
> "Option Explicit" is a directive to the compiler that tells it that you as the
> programmer are going to declare your variables. This line goes at the top of
> your module. All the subs and functions in that module will have to have all
> the variables declared.
>
> Then if you make a typing mistake:
>
> Dim lCtr as long
> lCtr = 1Ctr + 1
>
> You'll get an error since a mistyped variable wasn't Dimmed.
>
> Did you see the error in my example:
> One of those lCtr's is (ELL-ctr) and the other is (one)-ctr.
>
> Without that "option explicit" the top, excel will just figure you know what
> you're doing and meant to do that. But your code (probably) won't run as
> intended.
>
> These kinds of typos can be difficult to find. Forcing yourself to declare the
> variables will ease this kind of debugging problem.
>
> And a nice thing about declaring variables is you can use VBA's intellisense.
> If you do:
>
> dim Wks as worksheet
> set wks = worksheets("sheet1")
>
> then later type
>
> Wks.
> (note the dot)
> You'll see a list of all the possible methods and properties that go with that
> variable type (worksheet in my example).
>
> And another nice thing...
>
> If you have a variable like this:
>
> Dim myCounterOfReceiptsPaidOnTime as long
>
> You can type
>
> mycount
> and hit ctrl-spacebar.
> You'll see a list of all the stuff that starts with those characters and you can
> pick the one you want (or it's only this variable, it'll autocomplete for you).
>
> And I think most people who spend anytime writing macros have this option turned
> on automatically for all new modules (you'll still have to type it for existing
> modules).
>
> Inside the VBE
> Tools|Options|Editor Tab|Check "Require Variable Declaration"
>
> It's one of those things that may seem like too much work at the beginning, but
> it really makes life easier when you're typing and debugging.
>
>
> ==============
>
> I like to leave the cursor in the cell that I just changed. I use this setting:
> Tools|Options|edit|
> I leave the "Move selection after enter" unchecked.
>
> I like this for all my editting, though.
>
> If you don't want to change this setting, you can have your code go back to that
> D3 cell.
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim res As Variant
> If Target.Address <> "$D$3" Then Exit Sub
> res = Application.Match(Target.Value, Me.Range("B:B"), 0)
> If IsError(res) Then
> 'stay put
> 'beep '???
> Application.EnableEvents = False
> Target.Select
> Application.EnableEvents = True
> Else
> Me.Range("B:B")(res).Select
> End If
> End Sub
>
>
> Bob wrote:
> >
> > Dave, I am new to VB. I copied the code and getting an error with "Option
> > Explicit" if I remove it from the code the Goto works with a match, it does
> > not work if there is not a match and moves out of the cell. What may be wrong?
> >
> > "Dave Peterson" wrote:
> >
> > > Option Explicit
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim res As Variant
> > > If Target.Address <> "$D$3" Then Exit Sub
> > > res = Application.Match(Target.Value, Me.Range("B:B"), 0)
> > > If IsError(res) Then
> > > 'stay put
> > > 'beep '???
> > > Else
> > > Me.Range("B:B")(res).Select
> > > End If
> > > End Sub
> > >
> > >
> > > Bob wrote:
> > > >
> > > > I am using the following code to Goto the cell entered in cell "D3". It works
> > > > if there is a perfect match. If they do not match the code stops on line 3
> > > > with an error. What should be added to the code so that it will work when not
> > > > found and stay in cell "D3"? (With a pop of box "Number not Found" or
> > > > something like that)
> > > >
> > > > The column has numbers like 4512 and 4512-1 and 4512-01 to match.
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target.Address <> "$D$3" Then Exit Sub
> > > > Columns(2).Find(Target).Select
> > > > End Sub
> > > >
> > > > Thank for your help,
> > > >
> > > > Bob
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>