Looping on Cells $x$y (continued)
 

I have an worksheet with a Case structure for performing centain events is a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5. Is there a way
can set up the following, unfortunately it did not work for me so I assumed
it was not possibe or better yet I was doing something wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through various cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

You might mean

Select Case Target.Address
Case "$B$4:$B$5"
For Each cell In target
'do stuff
Next cell
End Select

or maybe

Select Case Target.Address
Case "$B$4", "$B$5"
If ....Then....End If
End Select

--

HTH

RP
(remove nothere from the email address if mailing direct)


"D.Parker" wrote in message
...
I have an worksheet with a Case structure for performing centain events is
a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5. Is there a way
can set up the following, unfortunately it did not work for me so I
assumed
it was not possibe or better yet I was doing something wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through various
cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

Cells(i,j).Address(True,True)

will produce the address for the cell at row i and column j in the form $A$1

But there may be a better way to do what you are trying to do -- however you
haven't said what that is.

--
Regards,
Tom Ogilvy




"D.Parker" wrote in message
...
I have an worksheet with a Case structure for performing centain events is
a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5. Is there a way
can set up the following, unfortunately it did not work for me so I
assumed
it was not possibe or better yet I was doing something wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through various
cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

I don't really understand your question. Could you provide a few
more details about what you are trying to accomplish, and why
adding more Case statements won't work?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"D.Parker" wrote in message
...
I have an worksheet with a Case structure for performing centain
events is a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5. Is
there a way
can set up the following, unfortunately it did not work for me
so I assumed
it was not possibe or better yet I was doing something wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through
various cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

My apologies. This is exactly what I want to do. The users can select
various cells and depending on which cells are selected routines will be
executed (i.e. if "$A$1" selected by user "do stuff", if "$A$5" selected "do
same stuff that was done when A1 was selected", if "$B$1" selected "do new
stuff" and if "$B$5" selected "do same new stuff that was done when B1 was
selected".

So I am currently replicating code and changing the cells numbers. I think
I am way off base.

Private Sub Worksheet_SelectionChange (Target....)

Select Target.Address
Case "$A$1", "$A$5"...
Do X

Case "$B$1", "$B$5"
Do Y
End Case

End Sub

I was hoping to loop through without the extremely long Case statment line
with exact cells. I hope this clarifies what I was trying to do. Is looping
possible? Thank you.

D.Parker

"Tom Ogilvy" wrote:




Cells(i,j).Address(True,True)

will produce the address for the cell at row i and column j in the form $A$1

But there may be a better way to do what you are trying to do -- however you
haven't said what that is.

--
Regards,
Tom Ogilvy




"D.Parker" wrote in message
...
I have an worksheet with a Case structure for performing centain events is
a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5. Is there a way
can set up the following, unfortunately it did not work for me so I
assumed
it was not possibe or better yet I was doing something wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through various
cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

As far as I can tell from your explanation, you're not way off
base. There really isn't a more compact (and maintainable) way to
do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"D.Parker" wrote in message
...
My apologies. This is exactly what I want to do. The users
can select
various cells and depending on which cells are selected
routines will be
executed (i.e. if "$A$1" selected by user "do stuff", if "$A$5"
selected "do
same stuff that was done when A1 was selected", if "$B$1"
selected "do new
stuff" and if "$B$5" selected "do same new stuff that was done
when B1 was
selected".

So I am currently replicating code and changing the cells
numbers. I think
I am way off base.

Private Sub Worksheet_SelectionChange (Target....)

Select Target.Address
Case "$A$1", "$A$5"...
Do X

Case "$B$1", "$B$5"
Do Y
End Case

End Sub

I was hoping to loop through without the extremely long Case
statment line
with exact cells. I hope this clarifies what I was trying to
do. Is looping
possible? Thank you.

D.Parker

"Tom Ogilvy" wrote:




Cells(i,j).Address(True,True)

will produce the address for the cell at row i and column j in
the form $A$1

But there may be a better way to do what you are trying to
do -- however you
haven't said what that is.

--
Regards,
Tom Ogilvy




"D.Parker" wrote in
message
...
I have an worksheet with a Case structure for performing
centain events is
a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5.
Is there a way
can set up the following, unfortunately it did not work for
me so I
assumed
it was not possibe or better yet I was doing something
wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through
various
cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

I was trying to eliminate extermely long lines of Case statements.

Case "$A$1", "$A$5", "$A$10", ...."$A$n"
Do....
Case "$B$1", "$B$5", "$B$10", ...."$B$n"
Do

This would continue through column L. I thought I could be more efficient.
Thank you for all of you assistanc.

D.Parker

"Chip Pearson" wrote:

I don't really understand your question. Could you provide a few
more details about what you are trying to accomplish, and why
adding more Case statements won't work?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"D.Parker" wrote in message
...
I have an worksheet with a Case structure for performing centain
events is a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5. Is
there a way
can set up the following, unfortunately it did not work for me
so I assumed
it was not possibe or better yet I was doing something wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through
various cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

How about

With Target
Select Case .Column
Case 2
Select Case .Row
Case 1 To 100
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"D.Parker" wrote in message
...
I was trying to eliminate extermely long lines of Case statements.

Case "$A$1", "$A$5", "$A$10", ...."$A$n"
Do....
Case "$B$1", "$B$5", "$B$10", ...."$B$n"
Do

This would continue through column L. I thought I could be more
efficient.
Thank you for all of you assistanc.

D.Parker

"Chip Pearson" wrote:

I don't really understand your question. Could you provide a few
more details about what you are trying to accomplish, and why
adding more Case statements won't work?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"D.Parker" wrote in message
...
I have an worksheet with a Case structure for performing centain
events is a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5. Is
there a way
can set up the following, unfortunately it did not work for me
so I assumed
it was not possibe or better yet I was doing something wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through
various cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

On further reflection, you could try something like the
following:

Select Case True
Case (Target.Column = 1) And _
(Not
(Application.IsError(Application.Match(Target.Row, Array(1, 5,
10, 15), 0))))
Debug.Print "A"
Case (Target.Column = 2) And _
(Not
(Application.IsError(Application.Match(Target.Row, Array(1, 5,
10, 15), 0))))
Debug.Print "B"
End Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"D.Parker" wrote in message
...
I was trying to eliminate extermely long lines of Case
statements.

Case "$A$1", "$A$5", "$A$10", ...."$A$n"
Do....
Case "$B$1", "$B$5", "$B$10", ...."$B$n"
Do

This would continue through column L. I thought I could be
more efficient.
Thank you for all of you assistanc.

D.Parker

"Chip Pearson" wrote:

I don't really understand your question. Could you provide a
few
more details about what you are trying to accomplish, and why
adding more Case statements won't work?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"D.Parker" wrote in
message
...
I have an worksheet with a Case structure for performing
centain
events is a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5.
Is
there a way
can set up the following, unfortunately it did not work for
me
so I assumed
it was not possibe or better yet I was doing something
wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop through
various cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

Or, simpler,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case True
Case (Target.Column = 1) And _
((Target.Row = 1) Or (Target.Row Mod 5 = 0))
Debug.Print "A"
Case (Target.Column = 2) And _
((Target.Row = 1) Or (Target.Row Mod 5 = 0))
Debug.Print "B"
End Select

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Chip Pearson" wrote in message
...
On further reflection, you could try something like the
following:

Select Case True
Case (Target.Column = 1) And _
(Not
(Application.IsError(Application.Match(Target.Row, Array(1, 5,
10, 15), 0))))
Debug.Print "A"
Case (Target.Column = 2) And _
(Not
(Application.IsError(Application.Match(Target.Row, Array(1, 5,
10, 15), 0))))
Debug.Print "B"
End Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"D.Parker" wrote in message
...
I was trying to eliminate extermely long lines of Case
statements.

Case "$A$1", "$A$5", "$A$10", ...."$A$n"
Do....
Case "$B$1", "$B$5", "$B$10", ...."$B$n"
Do

This would continue through column L. I thought I could be
more efficient.
Thank you for all of you assistanc.

D.Parker

"Chip Pearson" wrote:

I don't really understand your question. Could you provide a
few
more details about what you are trying to accomplish, and why
adding more Case statements won't work?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"D.Parker" wrote in
message
...
I have an worksheet with a Case structure for performing
centain
events is a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5.
Is
there a way
can set up the following, unfortunately it did not work for
me
so I assumed
it was not possibe or better yet I was doing something
wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop
through
various cells?

Thank you.

D. Parker

Looping on Cells $x$y (continued)
 

All:

Thank you so very much for your expertise !!!

Kind regards,

D.Parker

"Chip Pearson" wrote:

Or, simpler,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case True
Case (Target.Column = 1) And _
((Target.Row = 1) Or (Target.Row Mod 5 = 0))
Debug.Print "A"
Case (Target.Column = 2) And _
((Target.Row = 1) Or (Target.Row Mod 5 = 0))
Debug.Print "B"
End Select

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Chip Pearson" wrote in message
...
On further reflection, you could try something like the
following:

Select Case True
Case (Target.Column = 1) And _
(Not
(Application.IsError(Application.Match(Target.Row, Array(1, 5,
10, 15), 0))))
Debug.Print "A"
Case (Target.Column = 2) And _
(Not
(Application.IsError(Application.Match(Target.Row, Array(1, 5,
10, 15), 0))))
Debug.Print "B"
End Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"D.Parker" wrote in message
...
I was trying to eliminate extermely long lines of Case
statements.

Case "$A$1", "$A$5", "$A$10", ...."$A$n"
Do....
Case "$B$1", "$B$5", "$B$10", ...."$B$n"
Do

This would continue through column L. I thought I could be
more efficient.
Thank you for all of you assistanc.

D.Parker

"Chip Pearson" wrote:

I don't really understand your question. Could you provide a
few
more details about what you are trying to accomplish, and why
adding more Case statements won't work?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"D.Parker" wrote in
message
...
I have an worksheet with a Case structure for performing
centain
events is a
certain cell is selected:

Select Case Target.Address
Case "$B$4"
If ....Then....End If
End Select

So, now I would like to increment to, lets say to cell B5.
Is
there a way
can set up the following, unfortunately it did not work for
me
so I assumed
it was not possibe or better yet I was doing something
wrong?:

Instead of Case "$B$4" can I use Case "$i$j" to loop
through
various cells?

Thank you.

D. Parker