update range in For loop

[Guest]

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

 

[Guest]

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

 

[Zack Barresse]

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

 

[Guest]

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]

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?

 

[Guest]

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]

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?

 

[Guest]

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]

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.

 

[Guest]

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]

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?

 

[Guest]

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.

 

[Guest]

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

 

[Zack Barresse]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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