Macro HELP!

[Guest]

I need to update a spreadsheet that uses macros for invoice tracking. Here
is an example of what I am calculating:

Invoice 1 Task 1 Task 2 Task 3 Task 4
Company A $500 $1,250 $400 $375
Company B $220 $0 $546 $2,877

Invoice 2 Task 1 Task 2 Task 3 Task 4
Company A $300 $1,250 $400 $375
Company B $220 $0 $546 $2,877

Totals
Company A $700 $2,500 $800 $750
Cumulative Total
Company B $440 $0 $1,092 $5,754
Cumulative Total

I need to add a column (Task 5 etc.) and have that be included in the macro
for calculating ALL of Company A for each separate task, same for Company B.
The person who created this spreadsheet is no longer with us and I do not
have experience using Macros in Excel in order to add this column and other
future columns.

Thanks. -Christina

 

[Guest]

Can you post the existing code and we can help add what you need. Try Alt
F11 and look for the code in the available modules.

 

[Guest]

Here is one of the codes. It would appear that there is a code for each
company. Please keep in mind I know NOTHING about VB, so I don't understand
the values.

Sub Program()
Call GHTotals(a, b, c, d, e, f, g, h, i, j, k, l, m)
Call CDMTotals(a, b, c, d, e, f, g, h, i, j, k, l, m)
Call OTHERTotals(a, b, c, d, e, f, g, h, i, j, k, l, m)
Call OTHERTotals2(a, b, c, d, e, f, g, h, i, j, k, l, m)
End Sub

Sub GHTotals(a, b, c, d, e, f, g, h, i, j, k, l, m)

a = 0
b = 5
c = 1
d = 1
e = 0
f = 1
g = 0
h = 1
i = 0
j = 1
k = 0
l = 1
m = 0

Do Until c = ""
c = Worksheets("Monthly Invoices").Cells(10 + b, 10)
d = Worksheets("Monthly Invoices").Cells(10 + b, 11)
f = Worksheets("Monthly Invoices").Cells(10 + b, 12)
h = Worksheets("Monthly Invoices").Cells(10 + b, 13)
j = Worksheets("Monthly Invoices").Cells(10 + b, 14)
l = Worksheets("Monthly Invoices").Cells(10 + b, 15)
a = c + a
e = d + e
g = f + g
i = h + i
k = j + k
m = l + m
b = b + 6
a = a
e = e
g = g
i = i
k = k
m = m
Loop

Worksheets("Monthly Invoices").Cells(12 + b, 10) = a
Worksheets("Monthly Invoices").Cells(12 + b, 11) = e
Worksheets("Monthly Invoices").Cells(12 + b, 12) = g
Worksheets("Monthly Invoices").Cells(12 + b, 13) = i
Worksheets("Monthly Invoices").Cells(12 + b, 14) = k
Worksheets("Monthly Invoices").Cells(12 + b, 15) = m

End Sub


Sub CDMTotals(a, b, c, d, e, f, g, h, i, j, k, l, m)

a = 0
b = 5
c = 1
d = 1
e = 0
f = 1
g = 0
h = 1
i = 0
j = 1
k = 0
l = 1
m = 0

Do Until c = ""
c = Worksheets("Monthly Invoices").Cells(11 + b, 10)
d = Worksheets("Monthly Invoices").Cells(11 + b, 11)
f = Worksheets("Monthly Invoices").Cells(11 + b, 12)
h = Worksheets("Monthly Invoices").Cells(11 + b, 13)
j = Worksheets("Monthly Invoices").Cells(11 + b, 14)
l = Worksheets("Monthly Invoices").Cells(11 + b, 15)
a = c + a
e = d + e
g = f + g
i = h + i
k = j + k
m = l + m
b = b + 6
a = a
e = e
g = g
i = i
k = k
m = m
Loop

Worksheets("Monthly Invoices").Cells(15 + b, 10) = a
Worksheets("Monthly Invoices").Cells(15 + b, 11) = e
Worksheets("Monthly Invoices").Cells(15 + b, 12) = g
Worksheets("Monthly Invoices").Cells(15 + b, 13) = i
Worksheets("Monthly Invoices").Cells(15 + b, 14) = k
Worksheets("Monthly Invoices").Cells(15 + b, 15) = m

End Sub

Sub OTHERTotals(a, b, c, d, e, f, g, h, i, j, k, l, m)

a = 0
b = 5
c = 1
d = 1
e = 0
f = 1
g = 0
h = 1
i = 0
j = 1
k = 0
l = 1
m = 0

Do Until c = ""
c = Worksheets("Monthly Invoices").Cells(12 + b, 10)
d = Worksheets("Monthly Invoices").Cells(12 + b, 11)
f = Worksheets("Monthly Invoices").Cells(12 + b, 12)
h = Worksheets("Monthly Invoices").Cells(12 + b, 13)
j = Worksheets("Monthly Invoices").Cells(12 + b, 14)
l = Worksheets("Monthly Invoices").Cells(12 + b, 15)
a = c + a
e = d + e
g = f + g
i = h + i
k = j + k
m = l + m
b = b + 6
a = a
e = e
g = g
i = i
k = k
m = m
Loop

Worksheets("Monthly Invoices").Cells(18 + b, 10) = a
Worksheets("Monthly Invoices").Cells(18 + b, 11) = e
Worksheets("Monthly Invoices").Cells(18 + b, 12) = g
Worksheets("Monthly Invoices").Cells(18 + b, 13) = i
Worksheets("Monthly Invoices").Cells(18 + b, 14) = k
Worksheets("Monthly Invoices").Cells(18 + b, 15) = m

End Sub

Sub OTHERTotals2(a, b, c, d, e, f, g, h, i, j, k, l, m)

a = 0
b = 5
c = 1
d = 1
e = 0
f = 1
g = 0
h = 1
i = 0
j = 1
k = 0
l = 1
m = 0

Do Until c = ""
c = Worksheets("Monthly Invoices").Cells(13 + b, 10)
d = Worksheets("Monthly Invoices").Cells(13 + b, 11)
f = Worksheets("Monthly Invoices").Cells(13 + b, 12)
h = Worksheets("Monthly Invoices").Cells(13 + b, 13)
j = Worksheets("Monthly Invoices").Cells(13 + b, 14)
l = Worksheets("Monthly Invoices").Cells(13 + b, 15)
a = c + a
e = d + e
g = f + g
i = h + i
k = j + k
m = l + m
b = b + 6
a = a
e = e
g = g
i = i
k = k
m = m
Loop

Worksheets("Monthly Invoices").Cells(21 + b, 10) = a
Worksheets("Monthly Invoices").Cells(21 + b, 11) = e
Worksheets("Monthly Invoices").Cells(21 + b, 12) = g
Worksheets("Monthly Invoices").Cells(21 + b, 13) = i
Worksheets("Monthly Invoices").Cells(21 + b, 14) = k
Worksheets("Monthly Invoices").Cells(21 + b, 15) = m

End Sub

 

[Guest]

This is going to take some time to sort through and I don't have time right
now. Hopefully someone else can help before I get to it. I'm guessing this
could be cleaned up a lot.

Barb Reinhardt

 

[Guest]

I thought that might be the case unfortunately. This spreadsheet goes back
to 2005 (as do the invoices) and being that it is quite lengthy and shall
continue as long as the project is being invoiced, ANY help would be
appreciated.

 

[Guest]

Create a new test workbook (in case this code doesn't work right) and replace
the code you copied with this

Sub Program()
Call Totals("GH")
Call Totals("CDM")
Call Totals("Other")
Call Totals("Other2")
End Sub
Sub Totals(myString As String)
Dim myWS As Worksheet
Dim myOffset As Long
Dim myOffset1 As Long
Dim mySheetName As String

mySheetName = "Monthly Invoices"
On Error Resume Next
Set myWS = ThisWorkbook.Worksheets(mySheetName)
On Error GoTo 0
If myWS Is Nothing Then
MsgBox ("Worksheet " & mySheetName & " does not exist in this workbook")
Exit Sub
End If

If LCase(myString) = "gh" Then
myOffset = 10
myOffset1 = 12
ElseIf LCase(myString) = "cdm" Then
myOffset = 11
myOffset1 = 15
ElseIf LCase(myString) = "other" Then
myOffset = 12
myOffset1 = 18
ElseIf LCase(myString) = "other2" Then
myOffset = 13
myOffset1 = 21
End If

a = 0
b = 5
c = 1
d = 1
e = 0
f = 1
g = 0
h = 1
i = 0
j = 1
k = 0
l = 1
m = 0

Do Until c = ""
c = myWS.Cells(myOffset + b, 10)
d = myWS.Cells(myOffset + b, 11)
f = myWS.Cells(myOffset + b, 12)
h = myWS.Cells(myOffset + b, 13)
j = myWS.Cells(myOffset + b, 14)
l = myWS.Cells(myOffset + b, 15)
a = c + a
e = d + e
g = f + g
i = h + i
k = j + k
m = l + m
b = b + 6
'a = a
'e = e
'g = g
'i = i
'k = k
'm = m
Loop

myWS.Cells(myOffset1 + b, 10) = a
myWS.Cells(myOffset1 + b, 11) = e
myWS.Cells(myOffset1 + b, 12) = g
myWS.Cells(myOffset1 + b, 13) = i
myWS.Cells(myOffset1 + b, 14) = k
myWS.Cells(myOffset1 + b, 15) = m

End Sub

I think this substantially cleans it up. I don't understand the reasons for
all the variables a-m, but I didn't change those names. Try this and let
me know if you get the same result as your current macro. Once that's
working, I'll need to know where the data and results arestored for Tasks 1-5
for each company so I can figure out what the macro is really doing. FWIW, I
don't really like the code above.

 

[Guest]

This did not work, but I think I found an alternative to making this work.
Thanks so much for your time and effort.