Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused about referencing multiple object code rows in other worksheets?
I'm new to VBA coding in Excel and am trying to figure out how to do this, but I am running lost. Each month I will have a workbook containing various worksheets, each worksheet representing an individual account. The worksheets will be named by account number, like '123456'. I will then import a worksheet titled 'ocd 123456'. I need to create some code that recognizes the 'ocd 123456' worksheet and adds certain values together to copy to cells in the '123456' worksheet. There may be 10+ account numbers in each workbook, corresponding to an equal number of imported 'ocd ######' worksheets in the same workbook. The 'ocd 123456' worksheet will have a variable amount of rows depending on account and that month's transactions. The 'ocd 123456' worksheet will have two columns, column A with a three-digit object number followed by the corresponding object title and column B containing a monetary value. Additionally, a single cell in '123456' will likely need the sum of various object code values from multiple rows of 'ocd 123456'. For example: Cell C22 in '123456' should equal the sum of object codes 901-927 but not 921 (or 901-920 & 922-927). Each month will likely not have all of the these object codes, just a few. 123456 Code: -------------------- A B C 20 21 22 Equipment Purchases =(sum of object codes 901-920 & 922-927 from 'ocd 123456') 23 24 -------------------- ocd 123456 Code: -------------------- A B 1 001 - Salary 8,000 2 023 - Copies 20 3 901 - Laser Printers 50 4 908 - Light Bulbs 75 5 921 - Overhead 100 6 927 - Staplers 20 -------------------- Therefore, C22 in '123456' should equal 145 (50+75+20). I would geatly appreciate any help with this or any links or recommendations of books that may help me along the way. Thanks, Collin -- slunk ------------------------------------------------------------------------ slunk's Profile: http://www.excelforum.com/member.php...o&userid=31957 View this thread: http://www.excelforum.com/showthread...hreadid=516795 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused about referencing multiple object code rows in other worksheets?
Can someone please help me out? -- slunk ------------------------------------------------------------------------ slunk's Profile: http://www.excelforum.com/member.php...o&userid=31957 View this thread: http://www.excelforum.com/showthread...hreadid=516795 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused about referencing multiple object code rows in other worksheets?
Slunk,
You need to take the left 3 characters of the entry in column A - you can then categorise the entries. There are various ways to do it - here's a suggestion using Collections - using the object code as a 'key': Sub getTotals(aShtName As String) Dim inShtName As String Dim lastRow As Long Dim aRow As Long Dim objID As String Dim aValue As Double Dim aCollection As Collection Dim total As Double Dim x As Integer inShtName = "ocd " & aShtName Set aCollection = New Collection With Sheets(inShtName) ' get the last row lastRow = .Range("A65536").End(xlUp).Row ' then read in the values for each row For aRow = 1 To lastRow objID = Left(.Cells(aRow, "A"), 3) aValue = .Cells(aRow, "B") aCollection.Add key:=objID, Item:=aValue Next aRow End With ' now create totals on the required object codes total = 0 On Error Resume Next ' for object codes 901 to 920 For x = 901 To 920 total = total + aCollection(Format(x, "000")) Next x ' for object codes 920 to 927 For x = 922 To 927 total = total + aCollection(Format(x, "000")) Next x On Error GoTo 0 ' put the total in cell C22 Sheets(aShtName).Range("C22") = total Set aCollection = Nothing End Sub This could be called with: Sub testTotals() getTotals "123456" End Sub The above is with the different sheets in the same workbook. You would need to change the: With Sheets(inShtName) etc to something like: With Workbooks("myworkbook").Sheets(inShtName) to distinguish the different workbooks. This assumes that there will not be any duplicate object codes in your imported list. HTH Tim "slunk" wrote in message ... I'm new to VBA coding in Excel and am trying to figure out how to do this, but I am running lost. Each month I will have a workbook containing various worksheets, each worksheet representing an individual account. The worksheets will be named by account number, like '123456'. I will then import a worksheet titled 'ocd 123456'. I need to create some code that recognizes the 'ocd 123456' worksheet and adds certain values together to copy to cells in the '123456' worksheet. There may be 10+ account numbers in each workbook, corresponding to an equal number of imported 'ocd ######' worksheets in the same workbook. The 'ocd 123456' worksheet will have a variable amount of rows depending on account and that month's transactions. The 'ocd 123456' worksheet will have two columns, column A with a three-digit object number followed by the corresponding object title and column B containing a monetary value. Additionally, a single cell in '123456' will likely need the sum of various object code values from multiple rows of 'ocd 123456'. For example: Cell C22 in '123456' should equal the sum of object codes 901-927 but not 921 (or 901-920 & 922-927). Each month will likely not have all of the these object codes, just a few. 123456 Code: -------------------- A B C 20 21 22 Equipment Purchases =(sum of object codes 901-920 & 922-927 from 'ocd 123456') 23 24 -------------------- ocd 123456 Code: -------------------- A B 1 001 - Salary 8,000 2 023 - Copies 20 3 901 - Laser Printers 50 4 908 - Light Bulbs 75 5 921 - Overhead 100 6 927 - Staplers 20 -------------------- Therefore, C22 in '123456' should equal 145 (50+75+20). I would geatly appreciate any help with this or any links or recommendations of books that may help me along the way. Thanks, Collin -- slunk ------------------------------------------------------------------------ slunk's Profile: http://www.excelforum.com/member.php...o&userid=31957 View this thread: http://www.excelforum.com/showthread...hreadid=516795 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Referencing between multiple worksheets | Excel Discussion (Misc queries) | |||
Referencing cells in multiple worksheets | Excel Discussion (Misc queries) | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
Macro referencing multiple worksheets | Excel Discussion (Misc queries) | |||
Confused, again! Object variable not set? | Excel Programming |