Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with columns and for each
Hi Excel experts
I working on this code and i don't know why no run well. I have a lot data in the Current region Option Explicit Sub Test() Dim myrange As Range Dim mycell As Range Set myrange = Range("B2").CurrentRegion.Columns(1) For Each mycell In myrange mycell.Select Next mycell End Sub Thanks for your expert advice Regards Fernando |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with columns and for each
Hi Fernando,
The problem is caused by the fact that you are selecting just the column 1 within the currentregion. Thus, myCell is being treated as a column range, and so there is only one instance of myCell in myRange, they are the same range, so the loop only iterates once. Try this alternate version Dim myrange As Range Dim mycell As Range Set myrange = Range("B2", Range("B2").End(xlDown)) myrange.Select For Each mycell In myrange mycell.Select Next mycell -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Fernando Ortiz" wrote in message ... Hi Excel experts I working on this code and i don't know why no run well. I have a lot data in the Current region Option Explicit Sub Test() Dim myrange As Range Dim mycell As Range Set myrange = Range("B2").CurrentRegion.Columns(1) For Each mycell In myrange mycell.Select Next mycell End Sub Thanks for your expert advice Regards Fernando |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with columns and for each
Fernando,
I assume that you want your cells ( in the first column of your current region) selected one by one. If so, try : Option Explicit Sub Test() Dim NrRows As Integer, I As Integer Dim myrange As Range Set myrange = Range("B2").CurrentRegion.Columns(1) NrRows = myrange.Rows.Count For I = 1 To NrRows Cells(I, 1).Select Next End Sub -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "Fernando Ortiz" wrote in message ... Hi Excel experts I working on this code and i don't know why no run well. I have a lot data in the Current region Option Explicit Sub Test() Dim myrange As Range Dim mycell As Range Set myrange = Range("B2").CurrentRegion.Columns(1) For Each mycell In myrange mycell.Select Next mycell End Sub Thanks for your expert advice Regards Fernando |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with columns and for each
Or to avoid unnecessary selecting
Dim myrange As Range Dim mycell As Range Set myrange = Range("B2", Range("B2").End(xlDown)) 'myrange.Select For Each mycell In myrange.Cells mycell.Select Next mycell Not tested, Alan Beban Bob Phillips wrote: Hi Fernando, The problem is caused by the fact that you are selecting just the column 1 within the currentregion. Thus, myCell is being treated as a column range, and so there is only one instance of myCell in myRange, they are the same range, so the loop only iterates once. Try this alternate version Dim myrange As Range Dim mycell As Range Set myrange = Range("B2", Range("B2").End(xlDown)) myrange.Select For Each mycell In myrange mycell.Select Next mycell |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with columns and for each
Oops
That was something I threw in when testing, should have removed it before posting. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Alan Beban" wrote in message ... Or to avoid unnecessary selecting Dim myrange As Range Dim mycell As Range Set myrange = Range("B2", Range("B2").End(xlDown)) 'myrange.Select For Each mycell In myrange.Cells mycell.Select Next mycell Not tested, Alan Beban Bob Phillips wrote: Hi Fernando, The problem is caused by the fact that you are selecting just the column 1 within the currentregion. Thus, myCell is being treated as a column range, and so there is only one instance of myCell in myRange, they are the same range, so the loop only iterates once. Try this alternate version Dim myrange As Range Dim mycell As Range Set myrange = Range("B2", Range("B2").End(xlDown)) myrange.Select For Each mycell In myrange mycell.Select Next mycell |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with columns and for each
Experts:
Thanks a lot for your help Regards, Fernando Ortiz "Fernando Ortiz" escribió en el mensaje ... Hi Excel experts I working on this code and i don't know why no run well. I have a lot data in the Current region Option Explicit Sub Test() Dim myrange As Range Dim mycell As Range Set myrange = Range("B2").CurrentRegion.Columns(1) For Each mycell In myrange mycell.Select Next mycell End Sub Thanks for your expert advice Regards Fernando |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value trouble!!! | Excel Worksheet Functions | |||
IRR Trouble | Excel Worksheet Functions | |||
Trouble with IF | Excel Worksheet Functions | |||
trouble grouping rows/columns | Excel Discussion (Misc queries) | |||
Having trouble totaling columns in excel 2003, always worked b4 | Excel Worksheet Functions |