UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Should I Set Up This Array?    
 
   
PaulinRhody
post May 15 2014, 09:12 AM
Post#1



Posts: 113
Joined: 5-December 13
From: Rhode Island


I have a workbook with 200 worksheets. Each worksheet represents a "TI". For each TI, there is a TI Title, a principal investigator, a due date, and a period of performance. There is also a list of CDRLs that are line items describing the work to be done: a code, a title, and a due date.
ach month, a form (a separate spreadsheet) is sent to each TI task manager who will comment on the progress of the work. Currently, we have an analyst manually filling in all the previously mentioned fields. (Otherwise, the task managers just won't complete the form, because of the hassle.) I want to write code that will automate this process. Because the workbook is constantly being added to (new TI worksheets included) and edited, the analyst has to complete all new forms each month.
My thought is that I would pull the information into an array and then feed the information into this template, save as, and continue this to create the 200 files automatically. My first question is whether or not using an array sounds like an efficient way of doing this. My second question is how to create the array. Would this be a multidimensional array with the same number of dimensions as there are field in the form to be created?
Thanks
Go to the top of the page
 
ADezii
post May 15 2014, 10:54 AM
Post#2



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


I am not sure that I understand the exact nature of your request, but it appears as though you can use the Worksheets Collection of the Active Workbook to:
  1. Loop through all the Worksheets in the Active Workbook.
  2. If the Worksheet's Name <> the Active Worksheet's Name:
    1. Increment a Row Counter.
    2. Copy the Data from E1 of the Worksheet to a Sequential Row Number in Column A of the MASTER Sheet.

    CODE
    Dim wks As Excel.Worksheet
    Dim intRowNum As Integer
    For Each wks In ThisWorkbook.Worksheets
      If wks.Name <> ActiveSheet.Name Then
        intRowNum = intRowNum + 1
          Worksheets("Sheet4").Cells(intRowNum, 1) = wks.Range("E1").Value
      End If
    Next
  3. The above logic naturally assumes that specific information is located in exactly the same Cell(s) in each worksheet.
  4. If this is not what you are looking for, then I do apologize.
Go to the top of the page
 
PaulinRhody
post May 15 2014, 11:42 AM
Post#3



Posts: 113
Joined: 5-December 13
From: Rhode Island


I was just reading about Collections. I've never used them. I'll look into it.
The information is not in the same place every time. I wish it was.
Thanks AD
Go to the top of the page
 
PaulinRhody
post May 16 2014, 08:03 AM
Post#4



Posts: 113
Joined: 5-December 13
From: Rhode Island


My primary question is this: Which is more efficient performance,
. To gather the necessary information to fill out each form before ever beginning the form process. Gather the information into an array or collection, and then, when I have everything, go to the form and have the array or collection populate the forms.
For
2. Fill out the form as I iterate through each worksheet and don't worry about creating an array or collection.
I hope that makes sense.
Go to the top of the page
 
ADezii
post May 16 2014, 08:34 AM
Post#5



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


At this point, I would think that we need to see a Completed Form (Spreadsheet)? Can you Upload one to us?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 01:16 AM