Macro HELP!
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.
--
HTH,
Barb Reinhardt
"LttlFriend" wrote:
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
"Barb Reinhardt" wrote:
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.
--
HTH,
Barb Reinhardt
"LttlFriend" wrote:
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
|