update range in For loop

David · 04-12-2006, 02:10 PM

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

Duke Carey · 04-12-2006, 02:25 PM

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

Zack Barresse · 04-12-2006, 02:35 PM

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

David · 04-12-2006, 03:40 PM

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

Zack Barresse · 04-12-2006, 04:20 PM

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

David · 04-12-2006, 04:45 PM

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

Zack Barresse · 04-12-2006, 05:10 PM

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

David · 04-12-2006, 07:40 PM

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

Zack Barresse · 04-13-2006, 11:20 AM

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" <David@discussions.microsoft.com> wrote in message
news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> 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" <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")
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

David · 04-13-2006, 11:32 AM

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" <David@discussions.microsoft.com> wrote in message
> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> > 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" <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")
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

David · 04-13-2006, 11:36 AM

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" <David@discussions.microsoft.com> wrote in message
> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> > 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" <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")
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Zack Barresse · 04-13-2006, 11:45 AM

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" <David@discussions.microsoft.com> wrote in message
news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> 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" <David@discussions.microsoft.com> wrote in message
>> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
>> > 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" <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")
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

David · 04-13-2006, 12:35 PM

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" <David@discussions.microsoft.com> wrote in message
> news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> > 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" <David@discussions.microsoft.com> wrote in message
> >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> >> > 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" <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")
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

David · 04-13-2006, 05:25 PM

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" <David@discussions.microsoft.com> wrote in message
> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> > > 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" <David@discussions.microsoft.com> wrote in message
> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> > >> > 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" <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")
> > >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >>
> > >> >> >> >>
> > >> >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Zack Barresse · 04-14-2006, 04:00 PM

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" <David@discussions.microsoft.com> wrote in message
news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
> 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" <David@discussions.microsoft.com> wrote in message
>> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
>> > > 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" <David@discussions.microsoft.com> wrote in message
>> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
>> > >> > 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" <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")
>> > >> >> >> >> >
>> > >> >> >> >> >
>> > >> >> >> >>
>> > >> >> >> >>
>> > >> >> >> >>
>> > >> >> >>
>> > >> >> >>
>> > >> >> >>
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

David · 04-17-2006, 10:35 AM

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" <David@discussions.microsoft.com> wrote in message
> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
> > 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" <David@discussions.microsoft.com> wrote in message
> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> >> > > 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" <David@discussions.microsoft.com> wrote in message
> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> >> > >> > 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" <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

Zack Barresse · 04-17-2006, 10:50 AM

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" <David@discussions.microsoft.com> wrote in message
news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
> 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" <David@discussions.microsoft.com> wrote in message
>> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
>> > 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" <David@discussions.microsoft.com> wrote in message
>> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
>> >> > > 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" <David@discussions.microsoft.com> wrote in message
>> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
>> >> > >> > 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" <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

David · 04-17-2006, 11:55 AM

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" <David@discussions.microsoft.com> wrote in message
> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
> > 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" <David@discussions.microsoft.com> wrote in message
> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> >> >> > > 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" <David@discussions.microsoft.com> wrote in message
> >> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> >> >> > >> > 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" <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

Zack Barresse · 04-17-2006, 01:15 PM

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" <David@discussions.microsoft.com> wrote in message
news:76FEA89E-A8E2-435F-8092-54C7DD67EE50@microsoft.com...
> 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" <David@discussions.microsoft.com> wrote in message
>> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
>> > 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" <David@discussions.microsoft.com> wrote in message
>> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
>> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
>> >> >> > > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
>> >> >> > >> > 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" <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

David · 04-17-2006, 01:55 PM

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" <David@discussions.microsoft.com> wrote in message
> news:76FEA89E-A8E2-435F-8092-54C7DD67EE50@microsoft.com...
> > 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" <David@discussions.microsoft.com> wrote in message
> >> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
> >> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> >> >> >> > > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> >> >> >> > >> > 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" <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.

Zack Barresse · 04-17-2006, 02:40 PM

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

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

"David" <David@discussions.microsoft.com> wrote in message
news:B570F221-405D-4DAC-BC99-B8D52CB423F5@microsoft.com...
>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" <David@discussions.microsoft.com> wrote in message
>> news:76FEA89E-A8E2-435F-8092-54C7DD67EE50@microsoft.com...
>> > 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" <David@discussions.microsoft.com> wrote in message
>> >> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
>> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
>> >> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
>> >> >> >> > > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
>> >> >> >> > >> > 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" <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.

David · 04-17-2006, 02:45 PM

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" <David@discussions.microsoft.com> wrote in message
> news:B570F221-405D-4DAC-BC99-B8D52CB423F5@microsoft.com...
> >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" <David@discussions.microsoft.com> wrote in message
> >> news:76FEA89E-A8E2-435F-8092-54C7DD67EE50@microsoft.com...
> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
> >> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
> >> >> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> >> >> >> >> > > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
> >> >> >> >> > >> > 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

Zack Barresse · 04-17-2006, 03:35 PM

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" <David@discussions.microsoft.com> wrote in message
news:16F38DBF-A412-4BBA-9AD8-CF26A4F8347E@microsoft.com...
>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" <David@discussions.microsoft.com> wrote in message
>> news:B570F221-405D-4DAC-BC99-B8D52CB423F5@microsoft.com...
>> >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" <David@discussions.microsoft.com> wrote in message
>> >> news:76FEA89E-A8E2-435F-8092-54C7DD67EE50@microsoft.com...
>> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
>> >> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
>> >> >> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
>> >> >> >> >> > > 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" <David@discussions.microsoft.com> wrote in
>> >> >> >> >> > >> message
>> >> >> >> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
>> >> >> >> >> > >> > 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

David · 04-17-2006, 04:50 PM

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" <David@discussions.microsoft.com> wrote in message
> news:16F38DBF-A412-4BBA-9AD8-CF26A4F8347E@microsoft.com...
> >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" <David@discussions.microsoft.com> wrote in message
> >> news:B570F221-405D-4DAC-BC99-B8D52CB423F5@microsoft.com...
> >> >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" <David@discussions.microsoft.com> wrote in message
> >> >> news:76FEA89E-A8E2-435F-8092-54C7DD67EE50@microsoft.com...
> >> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
> >> >> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
> >> >> >> >> > 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" <David@discussions.microsoft.com> wrote in message
> >> >> >> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
> >> >> >> >> >> > > 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.
> >> >> >> >> >> > >>
> >> >> >> >> >> > >> --

Zack Barresse · 04-17-2006, 05:45 PM

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" <David@discussions.microsoft.com> wrote in message
news:B570F221-405D-4DAC-BC99-B8D52CB423F5@microsoft.com...
>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" <David@discussions.microsoft.com> wrote in message
>> news:76FEA89E-A8E2-435F-8092-54C7DD67EE50@microsoft.com...
>> > 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" <David@discussions.microsoft.com> wrote in message
>> >> news:F5B28097-9851-4463-9A69-D06CD8F28BF0@microsoft.com...
>> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> news:19EEABB8-057B-4120-8D4B-8C3B32F820FA@microsoft.com...
>> >> >> > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> >> > news:74012D13-674D-45F8-A5AA-B8C72EBCF82E@microsoft.com...
>> >> >> >> > > 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" <David@discussions.microsoft.com> wrote in message
>> >> >> >> > >> news:AF82EC9D-AA64-44CF-9B96-04B77B136E8C@microsoft.com...
>> >> >> >> > >> > 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" <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.

update range in For loop

LinkBack

Thread Tools

Rate This Thread

Display

update range in For loop

RE: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Re: update range in For loop

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions