Assign names to R1C1 referencing
 

I have a sheet that summarizes information from 52 other sheets. I want to check the values in the 53rd column to see if the value is 0. If not, go to the 52nd column and repeat. When it finds a value 0 (in column 30 for example), it will then take that value, plus values from 4 previous columns (26, 27, 28 & 29), and create a chart based on those values (5 week trendline)

I will be using loops to check for the values. After the first loop, the column number needs to be reduced by one. How can I do this

Art

Assign names to R1C1 referencing
 

Art,

This code snippet shows you how to find those 5 cells

Dim i As Long

For i = 53 To 5 Step -1
If Cells(1, i).Value 0 Then
Cells(1, i).Offset(0, -4).Resize(1, 5).Select
Exit For
End If
Next i


it assumes row 1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Art" wrote in message
...
I have a sheet that summarizes information from 52 other sheets. I want
to check the values in the 53rd column to see if the value is 0. If not,
go to the 52nd column and repeat. When it finds a value 0 (in column 30
for example), it will then take that value, plus values from 4 previous
columns (26, 27, 28 & 29), and create a chart based on those values (5 week
trendline).

I will be using loops to check for the values. After the first loop, the
column number needs to be reduced by one. How can I do this?

Art

Assign names to R1C1 referencing
 

rw = 21
for i = 53 to 1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy


"Art" wrote in message
...
I have a sheet that summarizes information from 52 other sheets. I want
to check the values in the 53rd column to see if the value is 0. If not,
go to the 52nd column and repeat. When it finds a value 0 (in column 30
for example), it will then take that value, plus values from 4 previous
columns (26, 27, 28 & 29), and create a chart based on those values (5 week
trendline).

I will be using loops to check for the values. After the first loop, the
column number needs to be reduced by one. How can I do this?

Art

Assign names to R1C1 referencing
 

Left out the Step part:

rw = 21
for i = 53 to 1 Step -1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
rw = 21
for i = 53 to 1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy


"Art" wrote in message
...
I have a sheet that summarizes information from 52 other sheets. I want
to check the values in the 53rd column to see if the value is 0. If not,
go to the 52nd column and repeat. When it finds a value 0 (in column 30
for example), it will then take that value, plus values from 4 previous
columns (26, 27, 28 & 29), and create a chart based on those values (5
week
trendline).

I will be using loops to check for the values. After the first loop,
the
column number needs to be reduced by one. How can I do this?

Art

Assign names to R1C1 referencing
 

Best not to loop back to column 1 when you are offsetting -4.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Ogilvy" wrote in message
...
Left out the Step part:

rw = 21
for i = 53 to 1 Step -1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
rw = 21
for i = 53 to 1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy


"Art" wrote in message
...
I have a sheet that summarizes information from 52 other sheets. I
want
to check the values in the 53rd column to see if the value is 0. If
not,
go to the 52nd column and repeat. When it finds a value 0 (in column
30
for example), it will then take that value, plus values from 4 previous
columns (26, 27, 28 & 29), and create a chart based on those values (5
week
trendline).

I will be using loops to check for the values. After the first loop,
the
column number needs to be reduced by one. How can I do this?

Art

Assign names to R1C1 referencing
 

True. You got me there, But then best not to do a 5 value trend if there
are not 5 values.

--
Regards,
Tom Ogilvy
"Bob Phillips" wrote in message
...
Best not to loop back to column 1 when you are offsetting -4.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Ogilvy" wrote in message
...
Left out the Step part:

rw = 21
for i = 53 to 1 Step -1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
rw = 21
for i = 53 to 1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy


"Art" wrote in message
...
I have a sheet that summarizes information from 52 other sheets. I
want
to check the values in the 53rd column to see if the value is 0. If
not,
go to the 52nd column and repeat. When it finds a value 0 (in column
30
for example), it will then take that value, plus values from 4
previous
columns (26, 27, 28 & 29), and create a chart based on those values (5
week
trendline).

I will be using loops to check for the values. After the first
loop,
the
column number needs to be reduced by one. How can I do this?

Art

Assign names to R1C1 referencing
 

Great! I now have it almost working, except for one thing. I have a second loop statement to repeat everything, but to put a second chart on the page 20 rows down. When the loop is on the second time around, it always stops at the same place giving the error "Run Time Error 1004, Method 'Cells' of object '_Global' failed. The code and the preceding line of code i

rw=rw+
if cells(rw,i)0 the

This is all part of the same macro
Any suggestions

Ar

----- Tom Ogilvy wrote: ----

Left out the Step part

rw = 2
for i = 53 to 1 Step -
if cells(rw,i) 0 the
set rng = cells(rw,i).offset(0,-4).Resize(1,5
exit fo
end i
Nex
msgbox rng.addres

--
Regards
Tom Ogilv

"Tom Ogilvy" wrote in messag
..
rw = 2
for i = 53 to
if cells(rw,i) 0 the
set rng = cells(rw,i).offset(0,-4).Resize(1,5
exit fo
end i
Nex
msgbox rng.addres
--
Regards
Tom Ogilv
"Art" wrote in messag
..
I have a sheet that summarizes information from 52 other sheets. I wan
to check the values in the 53rd column to see if the value is 0. If not
go to the 52nd column and repeat. When it finds a value 0 (in column 3
for example), it will then take that value, plus values from 4 previou
columns (26, 27, 28 & 29), and create a chart based on those values (
wee
trendline)
I will be using loops to check for the values. After the first loop
th
column number needs to be reduced by one. How can I do this
Ar

Assign names to R1C1 referencing
 

I suspect you have the chart selected

Try

if Activesheet.cells(rw,i)0 then

or specifically use the sheet name

With worksheets("Sheet3")
rw = 21
for i = 53 to 5 Step -1
if .cells(rw,i) 0 then
set rng = .cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
End With

as an example.

--
Regards,
Tom Ogilvy

"Art" wrote in message
...
Great! I now have it almost working, except for one thing. I have a
second loop statement to repeat everything, but to put a second chart on the
page 20 rows down. When the loop is on the second time around, it always
stops at the same place giving the error "Run Time Error 1004, Method
'Cells' of object '_Global' failed. The code and the preceding line of code
is

rw=rw+1
if cells(rw,i)0 then

This is all part of the same macro.
Any suggestions?

Art

----- Tom Ogilvy wrote: -----

Left out the Step part:

rw = 21
for i = 53 to 1 Step -1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
rw = 21
for i = 53 to 1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address
--
Regards,
Tom Ogilvy
"Art" wrote in message
...
I have a sheet that summarizes information from 52 other sheets.
I want
to check the values in the 53rd column to see if the value is 0.
If not,
go to the 52nd column and repeat. When it finds a value 0 (in
column 30
for example), it will then take that value, plus values from 4
previous
columns (26, 27, 28 & 29), and create a chart based on those values
(5
week
trendline).
I will be using loops to check for the values. After the first
loop,
the
column number needs to be reduced by one. How can I do this?
Art

Assign names to R1C1 referencing
 

I didn't have the chart selected, but the first example helped that part. Now I have a problem with the looping where it isn't looping properly, and I can't trace it out.

For J = 1 To 14 Step
For I = 53 To 6 Step -
If ActiveSheet.Cells(Rw, I) 0 The
Add chart code her
End I
Next
Next

This puts in the first chart (J=1) with no problem. When that chart is entered, it is supposed to go down one row in the datasheet (J=2), and insert another chart and place it 20 rows lower than the previous chart, and so on to the last row (J=14). It is only putting in the first chart. Any suggestions where I'm going wrong with the loops

Thanks
Ar

----- Tom Ogilvy wrote: ----

I suspect you have the chart selecte

Tr

if Activesheet.cells(rw,i)0 the

or specifically use the sheet nam

With worksheets("Sheet3"
rw = 2
for i = 53 to 5 Step -
if .cells(rw,i) 0 the
set rng = .cells(rw,i).offset(0,-4).Resize(1,5
exit fo
end i
Nex
End Wit

as an example

--
Regards
Tom Ogilv

"Art" wrote in messag
..
Great! I now have it almost working, except for one thing. I have
second loop statement to repeat everything, but to put a second chart on th
page 20 rows down. When the loop is on the second time around, it alway
stops at the same place giving the error "Run Time Error 1004, Metho
'Cells' of object '_Global' failed. The code and the preceding line of cod
i
rw=rw+
if cells(rw,i)0 the
This is all part of the same macro
Any suggestions
Ar
----- Tom Ogilvy wrote: ----
Left out the Step part
rw = 2
for i = 53 to 1 Step -
if cells(rw,i) 0 the
set rng = cells(rw,i).offset(0,-4).Resize(1,5
exit fo
end i
Nex
msgbox rng.addres
--
Regards
Tom Ogilv
"Tom Ogilvy" wrote in messag
..
rw = 2
for i = 53 to
if cells(rw,i) 0 the
set rng = cells(rw,i).offset(0,-4).Resize(1,5
exit fo
end i
Nex
msgbox rng.addres
--
Regards
Tom Ogilv
"Art" wrote in messag
..
I have a sheet that summarizes information from 52 other sheets
I wan
to check the values in the 53rd column to see if the value is 0
If not
go to the 52nd column and repeat. When it finds a value 0 (i
column 3
for example), it will then take that value, plus values from
previou
columns (26, 27, 28 & 29), and create a chart based on those value
(
wee
trendline)
I will be using loops to check for the values. After the firs
loop
th
column number needs to be reduced by one. How can I do this
Ar

Assign names to R1C1 referencing
 

Either change J to rw or rw to J

I changed rw to J

For J = 1 To 14 Step 1
For I = 53 To 6 Step -1
If ActiveSheet.Cells(J, I) 0 Then
Add chart code here
End If
Next I
Next J

--
Regards,
Tom Ogilvy



"Art" wrote in message
...
I didn't have the chart selected, but the first example helped that part.
Now I have a problem with the looping where it isn't looping properly, and I
can't trace it out.


This puts in the first chart (J=1) with no problem. When that chart is
entered, it is supposed to go down one row in the datasheet (J=2), and
insert another chart and place it 20 rows lower than the previous chart, and
so on to the last row (J=14). It is only putting in the first chart. Any
suggestions where I'm going wrong with the loops?

Thanks.
Art

----- Tom Ogilvy wrote: -----

I suspect you have the chart selected

Try

if Activesheet.cells(rw,i)0 then

or specifically use the sheet name

With worksheets("Sheet3")
rw = 21
for i = 53 to 5 Step -1
if .cells(rw,i) 0 then
set rng = .cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
End With

as an example.

--
Regards,
Tom Ogilvy

"Art" wrote in message
...
Great! I now have it almost working, except for one thing. I have
a
second loop statement to repeat everything, but to put a second chart
on the
page 20 rows down. When the loop is on the second time around, it
always
stops at the same place giving the error "Run Time Error 1004, Method
'Cells' of object '_Global' failed. The code and the preceding line
of code
is
rw=rw+1
if cells(rw,i)0 then
This is all part of the same macro.
Any suggestions?
Art
----- Tom Ogilvy wrote: -----
Left out the Step part:
rw = 21
for i = 53 to 1 Step -1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
rw = 21
for i = 53 to 1
if cells(rw,i) 0 then
set rng = cells(rw,i).offset(0,-4).Resize(1,5)
exit for
end if
Next
msgbox rng.address
--
Regards,
Tom Ogilvy
"Art" wrote in message
...
I have a sheet that summarizes information from 52 other sheets.
I want
to check the values in the 53rd column to see if the value is 0.
If not,
go to the 52nd column and repeat. When it finds a value 0 (in
column 30
for example), it will then take that value, plus values from 4
previous
columns (26, 27, 28 & 29), and create a chart based on those
values
(5
week
trendline).
I will be using loops to check for the values. After the first
loop,
the
column number needs to be reduced by one. How can I do this?
Art

Assign names to R1C1 referencing
 

Thanks Tom. Much better

Art