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 23rd, 2011, 11:09 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Two arrays within one loop

Hi

I have the macro below, how i am having two issues with it. One when it gets to the line hightlighted in red it produces a "Object doesn't support this property or method" error message.

Also the second problem i am happening is regarding the line i am getting an error on. I need the loop to that it loops through the path array but then depending on the path number puts in to the Sheet array so they line up. But i am sure how to do this.

Code:
Option Explicit
Option Base 1

Sub CallConsolidationV1()

Dim CCTWB As Workbook, CCTLWB As Workbook
Dim CCTWBSht, CCTLWBCSht, CCTLWBCLSht As Worksheet
Dim strFldr, Path1, Path2, Path3, Path4, Path5, Path6, Path7, Path8 As String
Dim CCMDates, CCYDates, X, L, Y As String
Dim StartTime, EndTime As Double
Dim SuggestCMonth, SuggestCYear As Integer
Dim PathArray, SheetArray As Variant

'Define Variables
Set CCTWB = Application.Workbooks.Open("C:\Documents and Settings\SeymourJ\Desktop\CallConsolidationAnalysisTemplate.xlsx")
Set CCTWBSht = CCTWB.Sheets("Data")

strFldr = "C:\Documents and Settings\SeymourJ\Desktop"

Path1 = "P:\Bentley Global\Composite\Global\apd files\07 Configuration Documents\Support"
Path2 = "P:\HondaBikesUK\Composites\apd files\07 Configuration Documents\Support"
Path3 = "P:\HondaCarsUK\Composite\Client Customisation\apd files\07 Configuration Documents\Support"
Path4 = "R:\BMWMotarradAU\Composite\apd files\07 Configuration Documents\Support"
Path5 = "R:\HondaCarsEurope\Markets\Belgium\Composites\07 Configuration Documents\Support"
Path6 = "R:\HondaCarsEurope\Markets\Netherlands\Composites\07 Configuration Documents\Support"
Path7 = "R:\IMGCarsUK\Composites\apd files\07 Configuration Documents\Support"
Path8 = "R:\MAN_ERF_UK\Composites\apd files\07 Configuration Documents\Support"

StartTime = Timer

'Gather Consolidation Dates
CCMDates = Application.InputBox("Please enter Consolidation Month", SuggestCMonth)
    If CCMDates = "" Or 0 Then
        MsgBox "Please enter a month figure"
        CCMDates = Application.InputBox("Please enter Consolidation Month", SuggestCMonth)
    End If

CCYDates = Application.InputBox("Please enter Consolidation Year", SuggestCYear)
    If CCYDates = "" Or 0 Then
        MsgBox "Please enter a month figure"
        CCYDates = Application.InputBox("Please enter Consolidation Year", SuggestCYear)
    End If

'Add dates to template
CCTWBSht.Range("B2").Value = CCMDates
CCTWBSht.Range("B3").Value = CCYDates

'Define Path Array
PathArray = Array(Path1, Path2, Path3, Path4, Path5, Path6, Path7, Path8)
SheetArray = Array("BGBL", "HUBK", "HUK", "BMW", "HBE", "HNL", "IMG", "MAN")

'Loop though path files and copy data
For X = LBound(PathArray) To UBound(PathArray)

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

    L = WorksheetFunction.CountA(CCTLWBCSht.Range("A:A"))
    
    CCTLWBCSht.Select

    CCTLWBCSht.Range("A" & L & "N" & L).Copy Destination:=CCTWBSht.Sheets(SheetArray).Range("A1")        
Next X

EndTime = Timer

MsgBox "Macro Finished in " & Round((EndTime - StartTime) / 60, 2) & " minutes."

End Sub

Last edited by jeskit; June 23rd, 2011 at 11:31 AM..
 
Old June 23rd, 2011, 11:27 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

Would be nice to see a red line somewhere :)
__________________
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 23rd, 2011, 11:32 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi...sorry about that altered the text now, you should be able to see the red text now...sorry
 
Old June 23rd, 2011, 11:36 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

Looks like you are trying to copy two differents formats (I mean the size are differents), so excel can't handle it..

it's that the case?
__________________
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 23rd, 2011, 11:38 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

The line in red should be taking the number from the worksheet function (L) and using it to give the line above the line number to copy so if L was 4 then it would be range a4:n4.

IIt should then copy that lien and paste it into cell A1 of the sheet which is defined in the sheet array
 
Old June 23rd, 2011, 12:06 PM
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

ahhh.. you are rigth.. the error is that you need a : to specify a range.. try with

CCTLWBCSht.Range("A" & L & ":N" & L).Copy
__________________
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 23rd, 2011, 12:09 PM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

hI

Thanks i had not noticed that!

However i am still getting the "Object doesn't support this property or method" error message.
 
Old June 24th, 2011, 03:08 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

I should have mentioned in my last post that i know why i am stilll getting that error message...Its to do with the fact i have sheet array in this line:

Code:
 Destination:=CCTWBSht.Sheets(SheetArray).Range("A1")
This is the 2nd issue i am having, i need to be able to have two arrays in the same loop, PathArray and SheetArray. But i am not sure how to do this., so i tried having putting sheetarray for the sheet name in a hope it would pick up the SheetArray Array but it hasn't.

I also figured it shouldn;t be CTTWBSht.sheets it should be CCTWB.sheets

Last edited by jeskit; June 24th, 2011 at 04:40 AM..
 
Old June 24th, 2011, 09:00 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

Well.. SheetArray is in fact an array, so what sheet are you expecting excel to use??

If your idea is use all of them, then you will have to manually round robin around all of them, and copy to all of them...
__________________
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 24th, 2011, 09:15 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Ok

Sheet Array is what i want it to use.

What i need is a loop which which opens all the workbooks in the located in the PathArray. Then for each of them copy the data range ("A" & L & "N" & L) and paste it in to sheets listed in the Sheet Array.

I need it to do this so the data gets pasted in the correct sheet, but i want to use a loop so i dont have write the same code out multiple times.

I hope that makes sense, i am not very good at explaining things





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.