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

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

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" <David@discussions.microsoft.com> wrote in message
news:2F623A5B-27E2-47E3-B172-64C8B921403E@microsoft.com...
> 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")
>
>

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" <David@discussions.microsoft.com> wrote in message
> news:2F623A5B-27E2-47E3-B172-64C8B921403E@microsoft.com...
> > 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")
> >
> >
>
>
>

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" <David@discussions.microsoft.com> wrote in message
news:C73EEB85-F579-4193-83C0-6E389A495DCE@microsoft.com...
> 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" <David@discussions.microsoft.com> wrote in message
>> news:2F623A5B-27E2-47E3-B172-64C8B921403E@microsoft.com...
>> > 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")
>> >
>> >
>>
>>
>>

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" <David@discussions.microsoft.com> wrote in message
> news:C73EEB85-F579-4193-83C0-6E389A495DCE@microsoft.com...
> > 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" <David@discussions.microsoft.com> wrote in message
> >> news:2F623A5B-27E2-47E3-B172-64C8B921403E@microsoft.com...
> >> > 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")
> >> >
> >> >
> >>
> >>
> >>
>
>
>

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" <David@discussions.microsoft.com> wrote in message
news:03135B6A-54C7-48B2-99AF-921A963D7D0C@microsoft.com...
> 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" <David@discussions.microsoft.com> wrote in message
>> news:C73EEB85-F579-4193-83C0-6E389A495DCE@microsoft.com...
>> > 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" <David@discussions.microsoft.com> wrote in message
>> >> news:2F623A5B-27E2-47E3-B172-64C8B921403E@microsoft.com...
>> >> > 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")
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>