Lines of code are being skipped

  • Hi There,


    I'm trying to write a macro that loops through all the new worksheets in a "master" workbook and copies the necessary data to another worksheet. Since the cells don't match completely, I have to map so that:


    the value in column A copies to column C
    the value in column C copies to column B
    the value in column N copies to column M


    The problem i'm having is that the loop is jumping certain lines of code I have written. I built an array as per another earlier thread which had a similar issue. However this problem is different as the macro has to loop through worksheets as well as looping whithin the worksheet itself. Please see code below...The code starts skipping as of the bolded lines....THANKS!


    [ufCODE]Sub WorksheetLoop()


    Dim WS_Count As Integer
    Dim I As Integer
    Dim x As Integer
    Dim j As Integer
    Dim LastRow As Long
    Dim ar As Variant
    Dim arr As Variant


    WS_Count = ActiveWorkbook.Worksheets.Count
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ar = Array("A", "C", "N")
    arrn = Array("C", "B", "M")

    Sheets("Margin Adj_Flex upl. F").Range("a4:t500").ClearContents

    For I = 4 To WS_Count
    For x = 19 To (LastRow - 1)
    If Range("L" & x) <> "" And Range("L" & x) <> "0" Then
    For j = 0 To 5
    Range(ar(j) & x).Copy Sheets("Margin Adj_Flex upl. F").Range(arr(j) & Rows.Count).End(xlUp)(2)
    Next j

    End If
    Next x
    Next I

    End Sub
    [/ufCODE]

  • Re: Lines of code are being skipped


    Hi Dangelor,


    Thanks for your post. The variable "LastRow" is intended to depend on column 1 of worksheet(I) that is being activated in the worksheet loop.

  • Re: Lines of code are being skipped


    Try this...

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Re: Lines of code are being skipped


    Thanks Dangelor!


    It reads well but then skips this part:


    Code
    For j = 0 To 5
                            .Range(ar(j) & x).Copy Sheets("Margin Adj_Flex upl. F").Range(arr(j) & Rows.Count).End(xlUp)(2)
                        Next j


    I'm not sure what I've done wrong there...


    Cheers!

  • Re: Lines of code are being skipped


    Array ar is loaded with "A", "C" and "N", and array arr is loaded with "C", "B" and "M".


    Therefore both have an LBound value of 0 and a UBound value of 2


    Then, in your code you use

    Code
    For j = 0 To 5
        .Range(ar(j) & x).Copy Sheets("Margin Adj_Flex upl. F").Range(arr(j) & Rows.Count).End(xlUp)(2)
    Next j


    as soon as j reaches a value of 3 (which will be the 4th iteration of the loop) you will get an error because ar(3) and arr(3) do not exist.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Lines of code are being skipped



    Thanks for the feedback Kjbox....i will adapt and get back to you....

  • Re: Lines of code are being skipped


    Thanks everyone...its reading fine now. No skipping...I can't believe I missed the "or" statement!


    Much obliged...

  • Re: Lines of code are being skipped


    PS....One last thing...its seems that the condition:


    If .Range("L" & x) <> "" And .Range("L" & x) <> "0" Then

    Is being ignored. Its returning all values and not filtering out for blanks or zeroes...


    something to do with my range not being defined perhaps?

  • Re: Lines of code are being skipped


    Hi dangelor,


    I believe the statement has to be "and" rather than "or" because the "or" statement will always be true and I just want to copy the cells that have a value in them. However, once I put the "and" statement in, it doesn't seem to recognize the two cells that have values...confusing!

  • Re: Lines of code are being skipped


    Try this ...

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Re: Lines of code are being skipped


    Wow dangelor, it works! Still trying to figure out what subtle changes you made...awesome stuff man, thanks!

  • Re: Lines of code are being skipped


    Read up on the With statement...


    This line

    Code
    If .Cells(x, 12) <> "" And .Cells(x, 12) <> 0 Then

    without the period before Cells, the code would look to the active worksheet for the cell, not the Worksheet (I).


    Good luck!

    If I've been helpful, let me know. If I haven't, let me know that too. 

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!