Macro HELP!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LttlFriend - 12 Sep 2007 22:08 GMT

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

Reply to this Message

Barb Reinhardt - 13 Sep 2007 02:38 GMT

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.

Signature

HTH,
Barb Reinhardt

> I need to update a spreadsheet that uses macros for invoice tracking. Here
> is an example of what I am calculating:
[quoted text clipped - 20 lines]
>
> Thanks. -Christina

Reply to this Message

LttlFriend - 13 Sep 2007 14:18 GMT

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

> 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.
[quoted text clipped - 23 lines]
> >
> > Thanks. -Christina

Reply to this Message

Barb Reinhardt - 13 Sep 2007 14:52 GMT

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

> 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
[quoted text clipped - 222 lines]
> > >
> > > Thanks. -Christina

Reply to this Message

LttlFriend - 13 Sep 2007 15:08 GMT

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.

> 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
[quoted text clipped - 228 lines]
> > > >
> > > > Thanks. -Christina

Reply to this Message

Barb Reinhardt - 14 Sep 2007 00:56 GMT

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.

Signature

HTH,
Barb Reinhardt

Reply to this Message

LttlFriend - 14 Sep 2007 15:02 GMT

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

> Create a new test workbook (in case this code doesn't work right) and replace
> the code you copied with this
[quoted text clipped - 286 lines]
> >
> > > Can you post the existing code and we can help add what you need. Try Alt

Reply to this Message