Macro HELP!
This did not work, but I think I found an alternative to making this work.
Thanks so much for your time and effort.
"Barb Reinhardt" wrote:
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
|