Re: Need some syntax help, please
From: Bernie Deitrick (_at_)
Date: 07/04/04
- Next message: Jonathan Stratford: "Sending text into the cell below"
- Previous message: gregork: "Another code for deleting row"
- In reply to: MARTY: "Need some syntax help, please"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 4 Jul 2004 11:36:02 -0400
Marty,
The first sub below calls the second, which sums the value from the sheet,
cell, and array of "GROUP?.xls" depending on what is passed to it. The
example will sum Sheet1 A1 from GROUP2, GROUP3, GROUP4, and GROUP5.xls, and
places the sum into cell A1 (the same cell as that being summed) of the
active sheet of the workbook where the code is located.
The GetSum macro can be called as many times as you want. To call it to sum
cells A1:A10 from Sheet1 of Group2, 3, 4 and 5, you could do this:
Dim Books As Variant
Dim myCell As Range
Books = Array(2, 3, 4, 5)
For Each myCell In Range("A1:A10")
'Call the summing macro
GetSum "Sheet1", myCell.Address, Books
Next myCell
HTH,
Bernie
MS Excel MVP
Sub PutSum()
Dim Books As Variant
Books = Array(2, 3, 4, 5)
'Call the summing macro
GetSum "Sheet1", "A1", Books
End Sub
Sub GetSum(SheetName As String, _
CellAdd As String, _
BookNums As Variant)
Dim N As Integer
Dim mySum As Double
For N = LBound(BookNums) To UBound(BookNums)
mySum = mySum + Workbooks("GROUP" & BookNums(N) & ".xls"). _
Worksheets(SheetName).Range(CellAdd).Value
Next N
ThisWorkbook.ActiveSheet.Range(CellAdd).Value = mySum
End Sub
"MARTY" <anonymous@discussions.microsoft.com> wrote in message
news:255a501c46143$87427880$a601280a@phx.gbl...
> Hello!
>
> I have 5 workbooks which contain data: GROUP1.xls ...
> GROUP5.xls. {NOTE: These names are not cast in stone; I
> can change them if needed to solve this problem. Read
> on.} Each workbook contains 5 worksheets. The books are
> identical in structure, i.e. the names of the 5
> worksheets are the same in all 5 workbooks, only the data
> on the sheets is different.
>
> I want to use a 6th workbook with VBA code to extract and
> combine data from the 5 workbooks (at times only two, but
> other times, 3, 4 or 5), but I can't figure out how to do
> it with a loop because I can't get the syntax correct.
>
> For example, assume cells A1 of Sheet1 of the 2,3,4 or 5
> workbooks contain a number. I want Cell A1 of the 6th
> book to contain the sum of those numbers. So I want to
> loop through each of the five, add the numbers together,
> and display the total in Cell A1 of the 6th book.
>
> I need something which will allow me to VARY the name of
> the workbook I'm extracting from but still have VBA
> recognize it. Here is what I mean:
>
> NumberBooks = {2, 3, 4 or 5}
> Dim MYSHEET, SixthBook as Object
> Set SixthBook = {active workbook sheet}
> For N = 1 to NumberBooks
> Set MYSHEET&N = Workbooks(Group&N.xls).Sheets("SheetName")
> SixthBook.Cells(#,#) = Workbooks(Group&N.xls).Sheets_
> ("SheetName")
> {addition operation}
> Next N
> {display operation}
>
> I know this isn't right, but hopefully you understand
> what I mean. I want to go from book to book using a for-
> next loop with the book name as a variable.
>
> Anybody know of a slick way to do this? Help is
> appreciated.
>
> OBTW: I currently have a hard-coded version of this
> operation which I had to complete to meet a deadline. It
> took 1547 lines of code. I have another project coming
> which will be similar, but different enough where I'll
> have to start from scratch. I'd like to avoid hardcoding
> again, hence the question. Thanks for your time.
>
> MARTY
>
- Next message: Jonathan Stratford: "Sending text into the cell below"
- Previous message: gregork: "Another code for deleting row"
- In reply to: MARTY: "Need some syntax help, please"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
