View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
David David is offline
external usenet poster
 
Posts: 1,560
Default update range in For loop

Thanks for the input Zack,
About selecting cells. How about, as I go through the cells when I color
them the first time, I insert values "lets say 0" Then after I copy/paste, I
look for all cells in specified rows (For loop on cells that have value 0)
for specified number of row.
One I have that range of cells then I can delete all zeros then perform what
I want on those cell (hide or lock all other cells). Is there any drawback
to this. would it take huge amout of time to process?

Thanks

"Zack Barresse" wrote:

No problem. I recommend any John Walkenbach book and Bill Jelen & Tracy
Syrstad's book isn't too bad either. Also take a hard look at Professional
Excel Development by Stephen Bullen & Rob Bovey. Although I have a strong
feeling that John W's Power Programming book would be a much better suit
than any of the others.

As far as *only* the colored cells, well, it's possible, but it'd be a
little more difficult than what we've got here. What you'd want to do is
know it's relative position in regards to your base (A1) and perform the
cut/paste on every single iteration as you step through both your loops. It
can't be done all at once, not like this, sorry.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
This works great, thank you.
One more question :) Is there a way to select only the colored cells so
I
can hide all the other cells?
Is there a good book you can suggest for me to buy.
I appreciate your help man.

"Zack Barresse" wrote:

Well, you could grab all four sides and use the entire region in your cut
....


Sub David_Test()
Dim i As Long, j As Long, rng As Range
Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 50 To 150
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < 50 Then
Cells(i, j).Interior.ColorIndex = 45
If rng Is Nothing Then
iBottom = i
iLeft = j
iRight = j
Set rng = Cells(i, j)
Else
iLeft = WorksheetFunction.Min(iLeft, j)
iRight = WorksheetFunction.Max(iRight, j)
Set rng = Union(Cells(i, j), rng)
iTop = i
End If
' Save
End If
Next
Next
If Not rng Is Nothing Then
MsgBox "Top: " & iTop & vbNewLine & _
"Bottom: " & iBottom & vbNewLine & _
"Left: " & iLeft & vbNewLine & _
"Right: " & iRight
' rng.Cut Destination:=Range("A1")
End If
Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")
End Sub


Is that what you're looking for?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
Hi Zack,
You mean that the Cut would not work with "Union" statment?
I am trying to create a circle and and then place it where ever I would
like
in the sheet, then do other things with cells in the circle.
Do you have an idea on how the grab that range as I go through the
loop?

Thanks for you help

"Zack Barresse" wrote:

Hmm, I see what you're trying to do, but the Cut method will not work
on
multiple selections like that. What is the purpose of this anyway?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
Hi guys,
Zack I tried your suggestion and it still did not work. inserted
your
code
in (rng =???) space in code below.
Sorry about the messed up code, I left out some pieces because the
only
part
that did not work was grabing that range. Then later cut/paste in
different
location. Here is the full code. All variables are declared and
not
show
in
this code

For i = 900 To 1100
For j = 50 To 150
d = Sqr((i - 1000) ^ 2 + (j - 100) ^ 2)
If d < 50 Then
Cells(i, j).Interior.ColorIndex = 45
'rng = ????? 'This is where I would like to get that range
and
save
End If

Next
Next
rng.Select
Selection.Cut Destination:=Range("A1")
End Sub

Thanks for your help


"Zack Barresse" wrote:

Hi there David,

Have a look at the Union method. You could probably use a simple
If/Then
statement with it ...

If rng Is Nothing Then
Set rng = Cells(i, j)
Else
Set rng = Union(Cells(i, j), rng)
End If

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"David" wrote in message
...
Hello,
Any help would be much appreciated.

I have 2 For loop that colors specific cells
I would like to save the range of colored cells each time through
loop
until
end.
then I cut range of cell and paste in different location.
This is code I have so far but I cannot get my hand on that range
of
cells.

Dim rng As Range
For i = 100 To 1000
For j = 50 to 150
Cells(i, j).Interior.ColorIndex = 40
rng = ?? 'here I would like to save specific cells in a
range
to
use
later
End If
Next
rng.Select
Selection.Cut Destination:=Range("A1")