PDA

View Full Version : select cells through loop



adygelber
10-31-2014, 04:47 AM
Hello all,

I want to select groups of cells with two loops, as you can see in the following script:



Dim nrSec As Long, nrOff As Long, nrWD As Long

nrSec = ThisWorkbook.Sheets("Input").Range("G2").Value
nrOff = ThisWorkbook.Sheets("Input").Range("D2").Value
nrWD = ThisWorkbook.Sheets("Input").Range("C3").Offset(, 8 + Month(Date)).Value



With ThisWorkbook.Sheets("Input").Range("D3", ThisWorkbook.Sheets("Input").Range("D" & Rows.Count).End(xlUp))

'first loop

For i = 1 To nrOff

fm = Application.CountIf(Worksheets("Input").Range("I:I"), Worksheets("Input").Range("E" & i + 2))
secxoff = .Cells(i).Offset(0, 2).Value

'second loop
For j = 1 To fm

p = p & "," & Range("C8").Offset(nrWD, secxoff + (j - 1) * 2).Address(0, 0)
Range(Mid$(p, 2)).Select

Next


Next


End With




Can anyone explain me why after is executed a complete cycle of the second loop the cells renmains selected? Practically, when the first loop is executed for the second time, the cells selected on the first execution remains selected.
I need the following mode of execution:

- on the first cycle the application has to select a group of cells (in base of some criterias as you can see in the script)
- on the second cycle the application must select another group of cells, but the cells selected on the first cycle has to be unselected
and so on...

I am also attaching the file.
Thank you!

PS: Posted also on ExcelForum and Ozgrid

Bob Phillips
10-31-2014, 05:02 AM
Because p is being appended to all of the time? Maybe you need to initialise p, p="", before the second loop starts.

adygelber
10-31-2014, 08:55 AM
Thank you very much!

That works perfect.