Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Need someone to help tweak a code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JB JB is offline
external usenet poster
  
Posts: 115
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
JB JB is offline
external usenet poster
  
Posts: 115
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
JB JB is offline
external usenet poster
  
Posts: 115
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA File Search Function tweak Nate[_3_] Excel Discussion (Misc queries) 1 December 7th 07 06:12 PM
SUMPRODUCT - Tweak Sam via OfficeKB.com Excel Worksheet Functions 2 September 6th 07 04:12 PM
Does anyone know how to tweak Custom Auto Filter box? [email protected] New Users to Excel 4 July 5th 07 03:48 PM
Array Help Tweak Luke Excel Worksheet Functions 13 November 22nd 06 10:29 AM
Can someone please tweak my Macro? Wibs Excel Discussion (Misc queries) 3 December 15th 05 05:10 PM


All times are GMT +1. The time now is 04:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement