Need someone to help tweak a code
 

I have the code below that jumps to another cell based on the data entered in
A2, but i need to tweak it so that it allows the data in A2 to be the result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia

Need someone to help tweak a code
 

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia

Need someone to help tweak a code
 

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia

Need someone to help tweak a code
 

I already included that in the version I posted Julia.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3",
"cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3",
"dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3",
"fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3",
"hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data
entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2
results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia

Need someone to help tweak a code
 

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson

Need someone to help tweak a code
 

Yes you did!

Bob Phillips wrote:

I already included that in the version I posted Julia.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JB" wrote in message
...
Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3",
"cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3",
"dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3",
"fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3",
"hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data
entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2
results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson

Need someone to help tweak a code
 

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson

Need someone to help tweak a code
 

It jumped for me.

What are you typing in A2?

Make sure that events are enabled and that macros are enabled.

And that you're typing that number in A2 of the sheet that has the code.

JB wrote:

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson


--

Dave Peterson

Need someone to help tweak a code
 

Hi Dave,

I'm not typing in A2, A2 includes a vlookup formula which results in a
number 1-52.
The first code I had (see below) worked if I typed the data directly into
A2, but it wouldn't work when A2 held the results of a formula.
I figured it would need just a little "tweaking" (sorry :))

Julia

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub



"Dave Peterson" wrote:

It jumped for me.

What are you typing in A2?

Make sure that events are enabled and that macros are enabled.

And that you're typing that number in A2 of the sheet that has the code.

JB wrote:

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson


--

Dave Peterson

Need someone to help tweak a code
 

Sorry, I didn't read the original post.

Option Explicit
Private Sub Worksheet_Calculate()

Dim Places As Variant
Dim i As Variant

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select
End Sub

====
Personally, I think that this would drive me nuts. Each time the worksheet
recalculated, I could be off to a new location.

JB wrote:

Hi Dave,

I'm not typing in A2, A2 includes a vlookup formula which results in a
number 1-52.
The first code I had (see below) worked if I typed the data directly into
A2, but it wouldn't work when A2 held the results of a formula.
I figured it would need just a little "tweaking" (sorry :))

Julia

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub



"Dave Peterson" wrote:

It jumped for me.

What are you typing in A2?

Make sure that events are enabled and that macros are enabled.

And that you're typing that number in A2 of the sheet that has the code.

JB wrote:

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Need someone to help tweak a code
 

No sorries, you are my only hope right now :)

I got an error message that reads:
"Compile Error: Only Comments may appear End Sub, End Function, or End
Property".

Julia

"Dave Peterson" wrote:

Sorry, I didn't read the original post.

Option Explicit
Private Sub Worksheet_Calculate()

Dim Places As Variant
Dim i As Variant

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select
End Sub

====
Personally, I think that this would drive me nuts. Each time the worksheet
recalculated, I could be off to a new location.

JB wrote:

Hi Dave,

I'm not typing in A2, A2 includes a vlookup formula which results in a
number 1-52.
The first code I had (see below) worked if I typed the data directly into
A2, but it wouldn't work when A2 held the results of a formula.
I figured it would need just a little "tweaking" (sorry :))

Julia

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub



"Dave Peterson" wrote:

It jumped for me.

What are you typing in A2?

Make sure that events are enabled and that macros are enabled.

And that you're typing that number in A2 of the sheet that has the code.

JB wrote:

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Need someone to help tweak a code
 

Nevermind, it was my fault....(i left a section of the old code in their by
accident)

IT WORKED PERFECTLY!!!! THANK YOU SOOOOO MUCH DAVE! :)

"Dave Peterson" wrote:

Sorry, I didn't read the original post.

Option Explicit
Private Sub Worksheet_Calculate()

Dim Places As Variant
Dim i As Variant

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select
End Sub

====
Personally, I think that this would drive me nuts. Each time the worksheet
recalculated, I could be off to a new location.

JB wrote:

Hi Dave,

I'm not typing in A2, A2 includes a vlookup formula which results in a
number 1-52.
The first code I had (see below) worked if I typed the data directly into
A2, but it wouldn't work when A2 held the results of a formula.
I figured it would need just a little "tweaking" (sorry :))

Julia

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub



"Dave Peterson" wrote:

It jumped for me.

What are you typing in A2?

Make sure that events are enabled and that macros are enabled.

And that you're typing that number in A2 of the sheet that has the code.

JB wrote:

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Need someone to help tweak a code
 

Glad it worked for you!

JB wrote:

Nevermind, it was my fault....(i left a section of the old code in their by
accident)

IT WORKED PERFECTLY!!!! THANK YOU SOOOOO MUCH DAVE! :)

"Dave Peterson" wrote:

Sorry, I didn't read the original post.

Option Explicit
Private Sub Worksheet_Calculate()

Dim Places As Variant
Dim i As Variant

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select
End Sub

====
Personally, I think that this would drive me nuts. Each time the worksheet
recalculated, I could be off to a new location.

JB wrote:

Hi Dave,

I'm not typing in A2, A2 includes a vlookup formula which results in a
number 1-52.
The first code I had (see below) worked if I typed the data directly into
A2, but it wouldn't work when A2 held the results of a formula.
I figured it would need just a little "tweaking" (sorry :))

Julia

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub



"Dave Peterson" wrote:

It jumped for me.

What are you typing in A2?

Make sure that events are enabled and that macros are enabled.

And that you're typing that number in A2 of the sheet that has the code.

JB wrote:

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3", "at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3", "ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3", "dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3", "fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3", "gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the data entered
in
A2, but i need to tweak it so that it allows the data in A2 to be the
result
of a formula and still jump to the specified cell based on the A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Need someone to help tweak a code
 

places = Array("J3","n3","r3","v3","z3","ad3","ah3"....<53

Hi. Just an idea. Placing 53 quoted strings on one line is a little hard
to read and print, as Dave was trying to show.
Here is just another idea:


Dim s As String
Dim Places

s = s & "J3 n3 r3 v3 z3 ad3 ah3 al3 ap3 at3 ax3 bb3 bf3 bj3 bn3 br3 bv3 bz3
cd3 "
s = s & "ch3 cl3 cp3 ct3 cx3 db3 df3 dj3 dn3 dr3 dv3 dz3 ed3 eh3 el3 ep3 et3
"
s = s & "ex3 fb3 ff3 fj3 fn3 fr3 fv3 fz3 gd3 gh3 gl3 gp3 gt3 gx3 hb3 hf3
hj3"

Places = Split(s)


- -
Dana DeLouis



"JB" wrote in message
...
Hi Dave,

I'm not typing in A2, A2 includes a vlookup formula which results in a
number 1-52.
The first code I had (see below) worked if I typed the data directly into
A2, but it wouldn't work when A2 held the results of a formula.
I figured it would need just a little "tweaking" (sorry :))

Julia

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub



"Dave Peterson" wrote:

It jumped for me.

What are you typing in A2?

Make sure that events are enabled and that macros are enabled.

And that you're typing that number in A2 of the sheet that has the code.

JB wrote:

I tried it, but it's not working....it won't jump :(

(I really appreciate this, is you have any more suggestions,
I'm willing to try anything at this point)

Julia

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Places As Variant
Dim i As Variant

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("a2")) Is Nothing Then
Exit Sub
End If

Places = Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", _
"al3", "ap3", "at3", "ax3", "bb3", "bf3", _
"bj3", "bn3", "br3", "bv3", "bz3", "cd3", _
"ch3", "cl3", "cp3", "ct3", "cx3", "db3", _
"df3", "dj3", "dn3", "dr3", "dv3", "dz3", _
"ed3", "eh3", "el3", "ep3", "et3", "ex3", _
"fb3", "ff3", "fj3", "fn3", "fr3", "fv3", _
"fz3", "gd3", "gh3", "gl3", "gp3", "gt3", _
"gx3", "hb3", "hf3", "hj3")

i = Me.Range("a2").Value

'minor validation--if not a nice number, then go to J3.
If IsNumeric(i) = False _
Or i UBound(Places) + 1 _
Or i <= LBound(Places) Then
i = LBound(Places) + 1
End If

If i = LBound(Places) Then
i = UBound(Places) + 1
End If

Me.Range(Places(i - 1)).Select

End Sub

JB wrote:

Thanks Bob, I just need a little more help (I don't know how to
check the
bounds :( )
Sorry to ask, but can you help?

Julia

"Bob Phillips" wrote:

Works for me with a formula, but you may want to do some bound
checks

Private Sub Worksheet_Change(ByVal Target As Range)
places = _
Array("J3", "n3", "r3", "v3", "z3", "ad3", "ah3", "al3", "ap3",
"at3",
"ax3", _
"bb3", "bf3", "bj3", "bn3", "br3", "bv3", "bz3", "cd3",
"ch3", "cl3",
_
"cp3", "ct3", "cx3", "db3", "df3", "dj3", "dn3", "dr3",
"dv3", "dz3",
_
"ed3", "eh3", "el3", "ep3", "et3", "ex3", "fb3", "ff3",
"fj3", "fn3",
_
"fr3", "fv3", "fz3", "gd3", "gh3", "gl3", "gp3", "gt3",
"gx3", "hb3",
"hf3", "hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
If i = LBound(places) And i <= UBound(places) Then
i = Range("a2").Value
Range(places(i - 1)).Select
End If
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail
in my addy)



"JB" wrote in message
...
I have the code below that jumps to another cell based on the
data entered
in
A2, but i need to tweak it so that it allows the data in A2 to
be the
result
of a formula and still jump to the specified cell based on the
A2 results.

Private Sub Worksheet_Change(ByVal Target As Range)
places =
Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3")
If Intersect(Target, Range("a2")) Is Nothing Then
Exit Sub
End If
i = Range("a2").Value
Range(places(i - 1)).Select
End Sub

Thanks in advance!
Julia




--

Dave Peterson


--

Dave Peterson