Assign names to R1C1 referencing

[Art]

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

 

[Bob Phillips]

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)

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?

 

[Tom Ogilvy]

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

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?

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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]

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

 

[Art]

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

 

[Tom Ogilvy]

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

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

 

[Art]

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

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

 

[Tom Ogilvy]

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

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?

 

[Art]

Thanks Tom. Much better

Art