Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 27th, 2011, 06:37 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

I have noticed no one has replied which is making think either i still have not explained it very well or you are all thinking about it or no one can help me.

So if its the first one, i am try to explain again as i am not very good at explaining things.

I have two arrays, PathArray and SheetArray. PathArray lists all of my variables which have been defined with path locations. SheetArray lists worksheets.

I need to use both of these arrays in one loop which uses the patharray to open the workbook within the locations and then use the sheetarray to define the sheet the data needs to be pasted into.

I hope this helps
 
Old June 27th, 2011, 08:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

I though it was very clear what you have to do...

You need to loop the second array in the same way you do with the first one.. So where is the problem??
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old June 27th, 2011, 08:13 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Thanks for replying!

currently i have, the code below where the first loop is for the PathArray and the second for the SheetArray:

Code:
For X = LBound(PathArray) To UBound(PathArray)

    For Y = LBound(SheetArray) To UBound(SheetArray)

        Set CCTLWB = Workbooks.Open(PathArray(X) & "\" & "*TrackerLog.*")
        Set CCTLWBCSht = CCTLWB.Sheets("Commentary")
        Set CCTLWBCLSht = CCTLWB.Sheets("Call Log")

        L = WorksheetFunction.CountA(CCTLWBCSht.Range("A:A"))
        
        CCTLWBCSht.Select
         
        CCTLWBCSht.Range("A" & L + 1 & ":N" & L + 1).Copy Destination:=CCTWB.Sheets(SheetArray(Y)).Range("A3")
   
        CCTLWBCLSht.Select
        
        CCTLWBCLSht.Range("A1:G1").AutoFilter
        
        Range("A1:G1").Select
        Selection.AutoFilter
        Range("B1").Select
        ActiveSheet.Range("$A$1:$G$14").AutoFilter Field:=2, Criteria1:="CCMDates"
        
        K = WorksheetFunction.CountA(CCTLWBCLSht.Range("B:B"))
        
        CCTLWBCLSht.Range("A2:G" & K - 1).Copy Destination:=CCTWB.Sheets(SheetArray(Y)).Range("A6")
        
        CCTLWB.Close
        
        'nextRow = CCTLWBCLSht.Range("A1048576").End(xlUp) + 1
        
        'If nextRow < CCMDates Then nextRow = CCMDates

        'For Each cellData In CCTLWBCLSht.Range("B" & L & ":B" & L)
         '   If cellData = CCMDates Then
         '       Rows(cellData.Row).Copy Destination:=CCTWB.Sheets(SheetArray(Y)).Cells(nextRow, 1)
        '        nextRow = nextRow + 1
       '     End If
        'Next cellData

    Next Y

Next X
However this code crashes on the second run of the loop, it produces an error of "Subscript out of range" also i noticed on the second run it opens the file in the same location as the first run of loop instead of moving to the next path location in the PathArray
 
Old June 27th, 2011, 08:14 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Thanks for replying!

currently i have, the code below where the first loop is for the PathArray and the second for the SheetArray:

Code:
For X = LBound(PathArray) To UBound(PathArray)

    For Y = LBound(SheetArray) To UBound(SheetArray)

        Set CCTLWB = Workbooks.Open(PathArray(X) & "\" & "*TrackerLog.*")
        Set CCTLWBCSht = CCTLWB.Sheets("Commentary")
        Set CCTLWBCLSht = CCTLWB.Sheets("Call Log")

        L = WorksheetFunction.CountA(CCTLWBCSht.Range("A:A"))
        
        CCTLWBCSht.Select
         
        CCTLWBCSht.Range("A" & L + 1 & ":N" & L + 1).Copy Destination:=CCTWB.Sheets(SheetArray(Y)).Range("A3")
   
        CCTLWBCLSht.Select
        
        CCTLWBCLSht.Range("A1:G1").AutoFilter
        
        Range("A1:G1").Select
        Selection.AutoFilter
        Range("B1").Select
        ActiveSheet.Range("$A$1:$G$14").AutoFilter Field:=2, Criteria1:="CCMDates"
        
        K = WorksheetFunction.CountA(CCTLWBCLSht.Range("B:B"))
        
        CCTLWBCLSht.Range("A2:G" & K - 1).Copy Destination:=CCTWB.Sheets(SheetArray(Y)).Range("A6")
        
        CCTLWB.Close
    Next Y

Next X
However this code crashes on the second run of the loop, it produces an error of "Subscript out of range" also i noticed on the second run it opens the file in the same location as the first run of loop instead of moving to the next path location in the PathArray
 
Old June 27th, 2011, 08:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

That's just a logic problem.. you are opening the file twice since you are looping in the second for and opening the file again ;)

review your second loop, maybe you have to open it before starting the second loop ;)
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old June 27th, 2011, 08:31 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

HI

I have tried opening the file before the second loop but i get an automation error message

Is there no way to loop though two arrays without having two loops?
 
Old June 27th, 2011, 08:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

No, How do you spect to do that?

Try this.. Instead of having 2 loops, try to make it work for the first item in the second loop, then add the code to loop in the second array...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old June 27th, 2011, 08:46 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

I wasn't sure if it was posisble, just asking.

Ok i'll try that
 
Old June 27th, 2011, 09:13 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

HI

I am still having trouble getting to work. I am not sure i am writing the code correctly.

I have tried adding the code to open the workbook and define the sheets before the secnd loop and it works on the first run but then on the second it ignores the code before the start of the second loop.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop And Arrays stevereimo Visual Basic 2008 Professionals 2 March 4th, 2010 10:17 AM
Loop twice, then inside loop select nodes?? JohnBampton XSLT 2 March 9th, 2009 05:21 AM
Multidemmesional Arrays OR arrays gmoney060 Classic ASP Basics 3 November 1st, 2004 03:42 PM
nested while loop doesn't loop hosefo81 PHP Databases 5 November 12th, 2003 08:46 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.