VBA question involving numerous additions

[MASON]

Hello

My job as an accountant involves producing a monthly gross
margin report for jobs . The cost of these jobs change from month to
month as costs come in . I need a macro that frees me up from adding
all these changes each month in an excel report . I am able to export
into excel a report from the accounting software that lists each job
that has costs for the month , some jobs may not have costs for a
given month . My gross margin report looks something like this .

COLUMN A COLUMN B COLUMN C

JOB NUMBER SALES PRICE MATERIAL COST ROW 1
ROW 2
98-1002 50,000.00 45,000.00 ROW 3
98-1003 25,000.00 7,500.00 ROW 4
98-1004 75,000.00 52,250.00 ROW 5

The formula for material cost for 98-1002 as an example is made up of
several months of activity (=+20000.00+15000.00+10000.00) . I need to
add the current month's changes to this formula .

The exported report from the accounting software is exported into the
gross margin report and looks like this .

COLUMN H COLUMN I ROW 1
ROW 2
98-1002 5,250.00 ROW 3
98-1004 1,250.00 ROW 4

Again this represents the current month's changes in costs I need to
add 5,250.00 to the formula that totals 45,000.00 and 1,250.00 to the
formula that totals 52,250.00 . Obviously if we were talking only a
few jobs this would not be a big deal but the number of jobs is
actually very large .

Once again any help is deeply appreciated .

Sinncerely Mason

 

[Peter Atherton]

Hello Mason

You should copy your data into another sheet before trying
this. It adds the New materials to the oringinal totals
where applicable.

Option Explicit
Sub addNewMat()
Dim tot As Range, newDat As Range
Dim r As Long, r2 As Long
Dim c, m
r = Application.WorksheetFunction.CountA(Range("A:A"))
r2 = Application.WorksheetFunction.CountA(Range("H:H"))
Set tot = Range(Cells(2, 1), Cells(r, 1))
Set newDat = Range(Cells(2, 8), Cells(r2, 8))
For Each m In newDat
For Each c In tot
c.Select
If c = m Then
c.Offset(0, 2) = c.Offset(0, 2) + m.Offset(0, 1)
End If
Next c
Next m

End Sub

It has only been tested on the limited data given.

Regards
Peter

-----Original Message-----
Hello

My job as an accountant involves producing a monthly gross
margin report for jobs . The cost of these jobs change from month to
month as costs come in . I need a macro that frees me up from adding
all these changes each month in an excel report . I am able to export
into excel a report from the accounting software that lists each job
that has costs for the month , some jobs may not have costs for a
given month . My gross margin report looks something like this .

COLUMN A COLUMN B COLUMN C

JOB NUMBER SALES PRICE MATERIAL COST ROW 1
ROW 2
98-1002 50,000.00 45,000.00 ROW 3
98-1003 25,000.00 7,500.00 ROW 4
98-1004 75,000.00 52,250.00 ROW 5

The formula for material cost for 98-1002 as an example is made up of
several months of activity

(=+20000.00+15000.00+10000.00) . I need to

 

[Don Guillett]

Where mynewdata is the column range containing the 98-1002,etc for the NEW
data and g3:g6 is the range containing the OLD data. Change to suit. This
will look at each cell in the NEW data column and compare to the OLD data
column. If it finds a match it will add the NEW data to the OLD data.

Sub AddNewValuesToOld()
For Each cel In [mynewdata]
With Worksheets("Sheet1").Range("g3:g6")
Set c = .Find(cel, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 2).Value = c.Offset(, 2) + cel.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

 

[Tushar Mehta]

No programming and minimal work...

Of course, you could program the process illustrated below by turning
on the macro recorder (Tools | Macro > Record new macro...) before
going through the steps by hand.

You may want to check out XL's Data | Consolidate... capability. When
you set up the specifications indicate that the sources sources are
entire columns. Create the consolidated result wherever (I tested with
L:N), and then, when you ready cut+paste to replace the existing
summary data with the new results.


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[MASON]

No programming and minimal work...

Of course, you could program the process illustrated below by turning
on the macro recorder (Tools | Macro > Record new macro...) before
going through the steps by hand.

You may want to check out XL's Data | Consolidate... capability. When
you set up the specifications indicate that the sources sources are
entire columns. Create the consolidated result wherever (I tested with
L:N), and then, when you ready cut+paste to replace the existing
summary data with the new results.


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks for the solutions every one . I will try these as soon as
I get back to work .
Regards Mason