range doesnt work for last varible

[ukyank]

Hi I am trying to get this print to loop thru several cells and plug them into another sheet to print out. The rang works until the last variable. even if I comment it out then the new last one errors out. Can someone tell me what I'm doing wrong:

Sub IteratePrint()
Dim r, clean, squat, bench As Range, lngLast As Long
lngLast = Sheets("MAXSHEET2").Range("C" & Rows.Count).End(xlUp).Row + 1

For Each r In Sheets("MAXSHEET2").Range("C2:C" & lngLast)
clean = Sheets("MAXSHEET2").Range("D2:D" & lngLast)
squat = Sheets("MAXSHEET2").Range("E2:E" & lngLast)
bench = Sheets("MAXSHEET2").Range("F2:F" & lngLast)
If Not IsEmpty(r) Then
Range("A8") = r
Range("E9") = clean 'Sheets("MAXSHEET2").Range("D" & lngLast).Value
Range("F9") = squat 'Sheets("MAXSHEET2").Range("E" & lngLast)
Range("G9") = bench 'Sheets("MAXSHEET2").Range("F" & lngLast)
ActiveSheet.PrintOut
End If
Next
End Sub

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[ukyank]

Thanks but this makes it a list it doesnt seem to replace the values

[ukyank]

Its probably not very pretty but I resolved it by using OFFSET:

If Not IsEmpty(r) Then
Range("A8") = r
Range("E9") = r.Offset(0, 1).Value
Range("F9") = r.Offset(0, 2).Value
Range("G9") = r.Offset(0, 3).Value
'ActiveSheet.PrintOut
End If

[Andy Pope]

It's unclear what you code should be doing.

First error I get is the assignment of bench variable. You have defined it as a range object. In which case you would need to use the Set assignment.

The variables clean, squat, bench are being loaded with an array of values. But when you write the values out you only specify a single cell. So only the first item is outputted.
In the loop you always load the same range into the variables so you always write out the same top row.

Describe what are you trying to do with the code.