UtterAccess Discussion Forums > I hope people work on Sunday...Excel For...Next

Full Version: I hope people work on Sunday...Excel For...Next

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

ian_john_walker

Mar 2 2008, 04:29 AM

Morning Chaps,

A tricky one for me, but probably not you guys!

I have a worksheet which contains rows of data, which are transferred onto a form and printed. The trick is there could be anything from 1 to 50 rows and any record with a Y in the end column needs to be copied and pasted, then printed and repeat...

I am trying to use For...Next without much success, can anyone help?

My code I have so far is:

For counter = 1 To 10 'changed to 10 from 50 to reduce time!

If Range("I12") = "Y" Then
Range("L12:O12").Select
Selection.Copy
Sheets("STF (linked)").Select
Range("R5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("STF linked master").Select

End If

Next counter

This sort of works but copies the same row repeatedly, I don't know how to move it onto the next row? Can anyone help?

Ian

argeedblu

Mar 2 2008, 04:59 AM

Ian,

I don't regularly work with Excel VBA but I think this solution should work for you. The problem is that you are refering to a specific cell and range in the second and third statements. (I12, L12:O12). You need to change those references on each iteration of the loop.

The first cell you want to check appears to be 11 greater than the index counter. So you need to calculate the Range by adding 11 to the counter. Try this:

If Range("I" & counter + 11) = "Y" then
Range ("L" & counter +11 & ":"O" & counter + 11).Select

Obviously, you would have to do a similar calculation to specify the destination range, unless you want to always want to paste to exactly the same destination.

Range("R" & counter + 4).Select

Glenn

ian_john_walker

Mar 2 2008, 05:19 AM

Morning Glenn,

This is brilliant, thank you! I had an idea this is where I was going wrong but just couldn't sort it!

Thanks for your help!

Ian

argeedblu

Mar 2 2008, 05:21 AM

You are very welcome, Ian.

Glenn

norie

Mar 2 2008, 09:05 AM

Try this.

CODE

For counter = 1 To 10 'changed to 10 from 50 to reduce time!

     Set rng =Sheets("STF linked master").Range("I" & counter)
     If rng.Value = "Y" Then
          rng.Resize(,4).Copy
          With Sheets("STF (linked)")
                .Range("R5").PasteSpecial Paste:=xlPasteValues
                .PrintOut Copies:=1, Collate:=True
          End With
    End If

Next counter

argeedblu

Mar 3 2008, 05:15 AM

Hmmm.

I think your code would need to be more like:

CODE

Dim counter As Integer
Dim rng As Range
    For counter = 1 To 10    'changed to 10 from 50 to reduce time!
        Set rng = Sheets("STF linked master").Range("I" & counter + 11)
        If rng.Value = "Y" Then
            'rng.Resize(, 4).Copy
            Set rng = Sheets("STF linked master").Range("L" & counter + 11 & ":" & "O" & counter + 11)
            rng.Copy
            With Sheets("STF (linked)")
                .Range("R5").PasteSpecial Paste:=xlPasteValues
                '.PrintOut Copies:=1, Collate:=True
            End With
        End If
    Next counter
End Sub

to meet Ian's original specification, Norie.

Glenn

norie

Mar 3 2008, 09:38 AM

Glenn

Oops!

Missed the +11 but I still think the resize is a little less cumbersome than concatenating to create the range to copy.

ian_john_walker

Mar 3 2008, 09:44 AM

Thanks both, the original suggestion was much easier and even I can almost understand it!!!

Regards

Ian

KingMartin

Mar 3 2008, 10:02 AM

Hi guys,

I know the issue has been solved, but we don't need to copy-paste, do we?

CODE

Const HOW_MANY_ROWS As Long = 10
Dim cl As Range
For Each cl In Sheets("STF linked master").Range("I12").Resize(HOW_MANY_ROWS)
    If cl.Value = "Y" Then
        Sheets("STF (linked)").Range("R5").Resize(4).Value = _
                Application.Transpose(cl.Offset(, 3).Resize(, 4))
            '.PrintOut Copies:=1, Collate:=True
    End If
Next

norie

Mar 3 2008, 11:11 AM

Martin

Why not? :(

I like copying and pasting, it's fun.

argeedblu

Mar 3 2008, 11:25 AM

Resizing didn't include the cells the op requires (columns L - O). Resizing would include I - L, wouldn't it?

Glenn

norie

Mar 3 2008, 11:29 AM

Glenn

That's right, especially without the +11 which I missed.

Oops, and another thing I missed - a little bit of Offset wouldn't do any harm.

That's what you get for writing air code and not testing it.

Edited by: norie on Mon Mar 3 11:31:05 EST 2008.

KingMartin

Mar 3 2008, 12:40 PM

Hi Glenn,

sorry for bringing even more confusion into this thread, but your question is directed at me?

This:

cl.Offset(, 3).Resize(, 4))

will return the range the OP wants, I think...

argeedblu

Mar 3 2008, 01:19 PM

No, Martin,

My reply was to Norie.

Your code appears to better Excel VBA than mine.

Glenn

ian_john_walker

Mar 3 2008, 01:32 PM

Now I'm really confused!! I think you've solved a problem which didn't exist!!

I'll leave you three to fight out for the resolution in just one line of code!!

Regards

Ian

KingMartin

Mar 3 2008, 01:34 PM

QUOTE

Your code appears to better Excel VBA than mine.

Well...

I just don't like to leave sheet in copy mode and have data on clipboard...

Range2.Value = Range1.Value

is quite fast and elegant way of how to copy values

Martin

KingMartin

Mar 3 2008, 01:37 PM

Hello Ian,

my first contribution to this thread was introduced by "I know the issue has been solved".

What we are discussing is rather "best practice". If the first code supplied by Glenn works for you, rejoice

However, you may be interested in alternative coding, as norie and me posted... that's all...

Martin

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.