Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro HELP!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro HELP!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro HELP!
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro HELP!
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 "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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro HELP!
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. "Barb Reinhardt" wrote: 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 "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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |