| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| reference cells when looping | Excel Discussion (Misc queries) | |||
| how do i prevent continued highlighting cells when cursor moves? | New Users to Excel | |||
| Looping on Cells with $x$y | Excel Programming | |||
| Looping through Cells | Excel Programming | |||
| Looping through a range of cells | Excel Programming | |||
Hybrid Mode
