update range in For loop
 

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")

update range in For loop
 

What is the End If related to?

Without the End If in there to cause ambiguity, I'd suggest you ID the range
as

set rng = Range(Cells(100, 50), Cells(1000, 150))
rng.Interior.ColorIndex = 40
rng.copy


"David" wrote:

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")

update range in For loop
 

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")

update range in For loop
 

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")

update range in For loop
 

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")

update range in For loop
 

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")

update range in For loop
 

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")

update range in For loop
 

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")

update range in For loop
 

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")

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")

update range in For loop
 

Well, adding another loop probably wouldn't be the greatest thing to do.
The more we can get rid of loops the better of we generally are. That being
said, sometimes there is just no way around them. If you can know the cells
relative position to where you are currently looping (coloring) then you can
just cut/paste inside your current loop structure and it wouldn't take much
to add to what you have, plus it wouldn't take any additional loops. Make
sense?

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


"David" wrote in message
...
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")

update range in For loop
 

Yes I understand, but I do have to go through a loop anyways in order to hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing to do.
The more we can get rid of loops the better of we generally are. That being
said, sometimes there is just no way around them. If you can know the cells
relative position to where you are currently looping (coloring) then you can
just cut/paste inside your current loop structure and it wouldn't take much
to add to what you have, plus it wouldn't take any additional loops. Make
sense?

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


"David" wrote in message
...
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")

update range in For loop
 

Hey

I added the following 2 For loops (end of code) after the code you suggested
to use.
Could please you tell me why these loops do not insert number "1" in the
cells that do not have value zero in them. What am I doing wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in order to hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing to do.
The more we can get rid of loops the better of we generally are. That being
said, sometimes there is just no way around them. If you can know the cells
relative position to where you are currently looping (coloring) then you can
just cut/paste inside your current loop structure and it wouldn't take much
to add to what you have, plus it wouldn't take any additional loops. Make
sense?

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


"David" wrote in message
...
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")

update range in For loop
 

Have you stepped through your code to observe what it's doing? Make use of
breakpoints with the F9 key in the VBE. Also use your Immediate window
(Ctrl + G) and you can use the line "Debug.Print Cells(row, col).Value" or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code you
suggested
to use.
Could please you tell me why these loops do not insert number "1" in the
cells that do not have value zero in them. What am I doing wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in order to
hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing to
do.
The more we can get rid of loops the better of we generally are. That
being
said, sometimes there is just no way around them. If you can know the
cells
relative position to where you are currently looping (coloring) then
you can
just cut/paste inside your current loop structure and it wouldn't take
much
to add to what you have, plus it wouldn't take any additional loops.
Make
sense?

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


"David" wrote in message
...
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")

update range in For loop
 

Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing? Make use of
breakpoints with the F9 key in the VBE. Also use your Immediate window
(Ctrl + G) and you can use the line "Debug.Print Cells(row, col).Value" or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code you
suggested
to use.
Could please you tell me why these loops do not insert number "1" in the
cells that do not have value zero in them. What am I doing wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in order to
hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing to
do.
The more we can get rid of loops the better of we generally are. That
being
said, sometimes there is just no way around them. If you can know the
cells
relative position to where you are currently looping (coloring) then
you can
just cut/paste inside your current loop structure and it wouldn't take
much
to add to what you have, plus it wouldn't take any additional loops.
Make
sense?

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


"David" wrote in message
...
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

update range in For loop
 

Fyi, you'll only need the quotes if it is text; if numeric, no quotes will
suffice. Post back if you need anything else. Keep up the good work. :)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing? Make use
of
breakpoints with the F9 key in the VBE. Also use your Immediate window
(Ctrl + G) and you can use the line "Debug.Print Cells(row, col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code you
suggested
to use.
Could please you tell me why these loops do not insert number "1" in
the
cells that do not have value zero in them. What am I doing wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in order
to
hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing to
do.
The more we can get rid of loops the better of we generally are.
That
being
said, sometimes there is just no way around them. If you can know
the
cells
relative position to where you are currently looping (coloring) then
you can
just cut/paste inside your current loop structure and it wouldn't
take
much
to add to what you have, plus it wouldn't take any additional loops.
Make
sense?

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


"David" wrote in message
...
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

update range in For loop
 

so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no quotes will
suffice. Post back if you need anything else. Keep up the good work. :)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing? Make use
of
breakpoints with the F9 key in the VBE. Also use your Immediate window
(Ctrl + G) and you can use the line "Debug.Print Cells(row, col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code you
suggested
to use.
Could please you tell me why these loops do not insert number "1" in
the
cells that do not have value zero in them. What am I doing wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in order
to
hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing to
do.
The more we can get rid of loops the better of we generally are.
That
being
said, sometimes there is just no way around them. If you can know
the
cells
relative position to where you are currently looping (coloring) then
you can
just cut/paste inside your current loop structure and it wouldn't
take
much
to add to what you have, plus it wouldn't take any additional loops.
Make
sense?

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


"David" wrote in message
...
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

update range in For loop
 

It works with quotes because you're looking at a string and not a numeric,
it's text.

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


"David" wrote in message
...
so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no quotes
will
suffice. Post back if you need anything else. Keep up the good work.
:)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing? Make
use
of
breakpoints with the F9 key in the VBE. Also use your Immediate
window
(Ctrl + G) and you can use the line "Debug.Print Cells(row,
col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code you
suggested
to use.
Could please you tell me why these loops do not insert number "1" in
the
cells that do not have value zero in them. What am I doing wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in
order
to
hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing
to
do.
The more we can get rid of loops the better of we generally are.
That
being
said, sometimes there is just no way around them. If you can
know
the
cells
relative position to where you are currently looping (coloring)
then
you can
just cut/paste inside your current loop structure and it wouldn't
take
much
to add to what you have, plus it wouldn't take any additional
loops.
Make
sense?

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


"David" wrote in message
...
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

update range in For loop
 

I understand what you mean.
1-First code where I insert a 0 numeric works,
Cells(row, col).Value = 0

When I check for the zero the code below does not work
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

2-
Cells(row, col).Value = "0"

When I check for the zero "text" the code below does works
If Cells(row, col).Value < "0" Then
Cells(row, col).Value = "1"

The question I should ask I guess is: why is numeric does not work but text
works?

Thanks for your help

"Zack Barresse" wrote:

It works with quotes because you're looking at a string and not a numeric,
it's text.

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


"David" wrote in message
...
so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no quotes
will
suffice. Post back if you need anything else. Keep up the good work.
:)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing? Make
use
of
breakpoints with the F9 key in the VBE. Also use your Immediate
window
(Ctrl + G) and you can use the line "Debug.Print Cells(row,
col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code you
suggested
to use.
Could please you tell me why these loops do not insert number "1" in
the
cells that do not have value zero in them. What am I doing wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in
order
to
hide
all the other cells. For this I will use relative addresses as you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest thing
to
do.
The more we can get rid of loops the better of we generally are.
That
being
said, sometimes there is just no way around them. If you can
know
the
cells
relative position to where you are currently looping (coloring)
then
you can
just cut/paste inside your current loop structure and it wouldn't
take
much
to add to what you have, plus it wouldn't take any additional
loops.
Make
sense?

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


"David" wrote in message
...
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.

update range in For loop
 

Do you have the format of the cells set to Text?

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


"David" wrote in message
...
I understand what you mean.
1-First code where I insert a 0 numeric works,
Cells(row, col).Value = 0

When I check for the zero the code below does not work
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

2-
Cells(row, col).Value = "0"

When I check for the zero "text" the code below does works
If Cells(row, col).Value < "0" Then
Cells(row, col).Value = "1"

The question I should ask I guess is: why is numeric does not work but
text
works?

Thanks for your help

"Zack Barresse" wrote:

It works with quotes because you're looking at a string and not a
numeric,
it's text.

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


"David" wrote in message
...
so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no quotes
will
suffice. Post back if you need anything else. Keep up the good work.
:)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing?
Make
use
of
breakpoints with the F9 key in the VBE. Also use your Immediate
window
(Ctrl + G) and you can use the line "Debug.Print Cells(row,
col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code
you
suggested
to use.
Could please you tell me why these loops do not insert number "1"
in
the
cells that do not have value zero in them. What am I doing
wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As
Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut
Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message
box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in
order
to
hide
all the other cells. For this I will use relative addresses as
you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest
thing
to
do.
The more we can get rid of loops the better of we generally
are.
That
being
said, sometimes there is just no way around them. If you can
know
the
cells
relative position to where you are currently looping
(coloring)
then
you can
just cut/paste inside your current loop structure and it
wouldn't
take
much
to add to what you have, plus it wouldn't take any additional
loops.
Make
sense?

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


"David" wrote in message
...
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.

update range in For loop
 

I do not believe so.
How would you check that?

"Zack Barresse" wrote:

Do you have the format of the cells set to Text?

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


"David" wrote in message
...
I understand what you mean.
1-First code where I insert a 0 numeric works,
Cells(row, col).Value = 0

When I check for the zero the code below does not work
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

2-
Cells(row, col).Value = "0"

When I check for the zero "text" the code below does works
If Cells(row, col).Value < "0" Then
Cells(row, col).Value = "1"

The question I should ask I guess is: why is numeric does not work but
text
works?

Thanks for your help

"Zack Barresse" wrote:

It works with quotes because you're looking at a string and not a
numeric,
it's text.

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


"David" wrote in message
...
so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no quotes
will
suffice. Post back if you need anything else. Keep up the good work.
:)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing?
Make
use
of
breakpoints with the F9 key in the VBE. Also use your Immediate
window
(Ctrl + G) and you can use the line "Debug.Print Cells(row,
col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code
you
suggested
to use.
Could please you tell me why these loops do not insert number "1"
in
the
cells that do not have value zero in them. What am I doing
wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As
Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut
Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message
box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in
order
to
hide
all the other cells. For this I will use relative addresses as
you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest
thing
to
do.
The more we can get rid of loops the better of we generally
are.
That
being
said, sometimes there is just no way around them. If you can
know
the
cells
relative position to where you are currently looping
(coloring)
then
you can
just cut/paste inside your current loop structure and it
wouldn't
take
much
to add to what you have, plus it wouldn't take any additional
loops.
Make
sense?

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


"David" wrote in message
...
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

update range in For loop
 

Select a cell, press Ctrl + 1, select the Number tab, it will be highlighted
in the left listbox.

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


"David" wrote in message
...
I do not believe so.
How would you check that?

"Zack Barresse" wrote:

Do you have the format of the cells set to Text?

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


"David" wrote in message
...
I understand what you mean.
1-First code where I insert a 0 numeric works,
Cells(row, col).Value = 0

When I check for the zero the code below does not work
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

2-
Cells(row, col).Value = "0"

When I check for the zero "text" the code below does works
If Cells(row, col).Value < "0" Then
Cells(row, col).Value = "1"

The question I should ask I guess is: why is numeric does not work but
text
works?

Thanks for your help

"Zack Barresse" wrote:

It works with quotes because you're looking at a string and not a
numeric,
it's text.

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


"David" wrote in message
...
so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no
quotes
will
suffice. Post back if you need anything else. Keep up the good
work.
:)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing?
Make
use
of
breakpoints with the F9 key in the VBE. Also use your Immediate
window
(Ctrl + G) and you can use the line "Debug.Print Cells(row,
col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code
you
suggested
to use.
Could please you tell me why these loops do not insert number
"1"
in
the
cells that do not have value zero in them. What am I doing
wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As
Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter
Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut
Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with
message
box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways
in
order
to
hide
all the other cells. For this I will use relative addresses
as
you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest
thing
to
do.
The more we can get rid of loops the better of we generally
are.
That
being
said, sometimes there is just no way around them. If you
can
know
the
cells
relative position to where you are currently looping
(coloring)
then
you can
just cut/paste inside your current loop structure and it
wouldn't
take
much
to add to what you have, plus it wouldn't take any
additional
loops.
Make
sense?

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


"David" wrote in message
...
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

update range in For loop
 

what is highlighted is general not text.
so, I guess the format of the cells is not set to Text.

Thanks
"Zack Barresse" wrote:

Select a cell, press Ctrl + 1, select the Number tab, it will be highlighted
in the left listbox.

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


"David" wrote in message
...
I do not believe so.
How would you check that?

"Zack Barresse" wrote:

Do you have the format of the cells set to Text?

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


"David" wrote in message
...
I understand what you mean.
1-First code where I insert a 0 numeric works,
Cells(row, col).Value = 0

When I check for the zero the code below does not work
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

2-
Cells(row, col).Value = "0"

When I check for the zero "text" the code below does works
If Cells(row, col).Value < "0" Then
Cells(row, col).Value = "1"

The question I should ask I guess is: why is numeric does not work but
text
works?

Thanks for your help

"Zack Barresse" wrote:

It works with quotes because you're looking at a string and not a
numeric,
it's text.

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


"David" wrote in message
...
so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no
quotes
will
suffice. Post back if you need anything else. Keep up the good
work.
:)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing?
Make
use
of
breakpoints with the F9 key in the VBE. Also use your Immediate
window
(Ctrl + G) and you can use the line "Debug.Print Cells(row,
col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code
you
suggested
to use.
Could please you tell me why these loops do not insert number
"1"
in
the
cells that do not have value zero in them. What am I doing
wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As
Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter
Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut
Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with
message
box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways
in
order
to
hide
all the other cells. For this I will use relative addresses
as
you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest
thing
to
do.
The more we can get rid of loops the better of we generally
are.
That
being
said, sometimes there is just no way around them. If you
can
know
the
cells
relative position to where you are currently looping
(coloring)
then
you can
just cut/paste inside your current loop structure and it
wouldn't
take
much
to add to what you have, plus it wouldn't take any
additional
loops.
Make
sense?

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


"David" wrote in message
...
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.

--

update range in For loop
 

You might then try ..

If Clng(Cells(row, col).Value) < 0 Then
Cells(row, col).Value = 1

... making use of the coerce/long function.

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


"David" wrote in message
...
I understand what you mean.
1-First code where I insert a 0 numeric works,
Cells(row, col).Value = 0

When I check for the zero the code below does not work
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

2-
Cells(row, col).Value = "0"

When I check for the zero "text" the code below does works
If Cells(row, col).Value < "0" Then
Cells(row, col).Value = "1"

The question I should ask I guess is: why is numeric does not work but
text
works?

Thanks for your help

"Zack Barresse" wrote:

It works with quotes because you're looking at a string and not a
numeric,
it's text.

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


"David" wrote in message
...
so, when I use the quotes, things work,
when I do not, they it does not work. I have no idea why.
I will keep trying.
Thanks

"Zack Barresse" wrote:

Fyi, you'll only need the quotes if it is text; if numeric, no quotes
will
suffice. Post back if you need anything else. Keep up the good work.
:)

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


"David" wrote in message
...
Hi Zack,
I was missing the quotes on "0" and "1"
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1

I am trying to hide those cells now
Thanks

"Zack Barresse" wrote:

Have you stepped through your code to observe what it's doing?
Make
use
of
breakpoints with the F9 key in the VBE. Also use your Immediate
window
(Ctrl + G) and you can use the line "Debug.Print Cells(row,
col).Value"
or
some such value in your code and it will appear in your IW.

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


"David" wrote in message
...
Hey

I added the following 2 For loops (end of code) after the code
you
suggested
to use.
Could please you tell me why these loops do not insert number "1"
in
the
cells that do not have value zero in them. What am I doing
wrong?

Dim i As Long, j As Long, rng As Range, rng1 As Range

Dim iTop As Long, iLeft As Long, iBottom As Long, iRight As
Long
For i = 900 To 1100
For j = 0 To 250
If Sqr((i - 1000) ^ 2 + (j - 100) ^ 2) < diameter Then
Cells(i, j).Interior.ColorIndex = 38
Cells(i, j).Value = "0"
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
End If

Range(Cells(iBottom, iLeft), Cells(iTop, iRight)).Cut
Range("A1")

Dim row As Integer, col As Integer
Dim RowsCircle As Integer, ColCircle As Integer
RowsCircle = (iTop - iBottom + 1) ' = 99 Checked with message
box
ColCircle = (iRight - iLeft + 1) ' = 99
For row = 1 To RowsCircle
For col = 1 To ColCircle
If Cells(row, col).Value < 0 Then
Cells(row, col).Value = 1
End If
Next
Next

Thanks


"David" wrote:

Yes I understand, but I do have to go through a loop anyways in
order
to
hide
all the other cells. For this I will use relative addresses as
you
said(top-right, right -left).
You will see a note out if I get stuck :)
Thanks for your help.

"Zack Barresse" wrote:

Well, adding another loop probably wouldn't be the greatest
thing
to
do.
The more we can get rid of loops the better of we generally
are.
That
being
said, sometimes there is just no way around them. If you can
know
the
cells
relative position to where you are currently looping
(coloring)
then
you can
just cut/paste inside your current loop structure and it
wouldn't
take
much
to add to what you have, plus it wouldn't take any additional
loops.
Make
sense?

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


"David" wrote in message
...
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.