Confused about referencing multiple object code rows in other worksheets?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

slunk - 27 Feb 2006 07:12 GMT

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

Signature

slunk

Reply to this Message

slunk - 28 Feb 2006 01:37 GMT

Can someone please help me out? :confused:

Signature

slunk

Reply to this Message

Tim Barlow - 28 Feb 2006 21:33 GMT

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

> I'm new to VBA coding in Excel and am trying to figure out how to do
> this, but I am running lost.
[quoted text clipped - 57 lines]
> Thanks,
> Collin

Reply to this Message