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

Thanks Dangelor!

It reads well but then skips this part:

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

Thanks Dangelor....well observed

Re: Lines of code are being skipped

Quote from KjBox;794112

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.

Display More

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

I'd love to...please see attached...

thanks a million

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

Damn! I would have never figured that out. Much obliged Sir!