Macro running, but only giving zeroes

[Shushanna]

I have two tabs in an excel file. I've defined them as shop (the shop doing the work) and jon (job order number). "Shop" shows the money that each shop has spent against each job order number. "Jon" shows the total bugetted for each job order number. I want the macro to go down the list of job order numbers on "jon" (jon column B) and check "shop"s job order numbers (shop column B) for the totals spent per shop (shop column J), and input the total spent per job order number in jon column E.

A complication is that shop column J, the totals spent per shop for a job order number, is a calculation - not just a value.

Here is my code. It's filling in the right column in the jon tab, but it's only giving me 0 for all of the values. Any idea what I'm doing wrong?

Sub total_spent_jon()
'The purpose of this Macro is to sum the amont spent per shop for a JON on DWU_Shop
'and list that sum on DWU_Jon_Balance, and then calculate the Remaining.

Dim shop As Worksheet 'This is the worksheet that lists totals spent per shop
Set shop = Sheets("DWU_Shop")

Dim jon As Worksheet 'This is the worksheet that lists the total budgetted per Jon
Set jon = Sheets("DWU_JON_Balance")

Dim sRow As Long 'row index on shop worksheet
Dim jRow As Long 'row index on jon worksheet
Dim sCount As Long
sCount = 0
Dim jCount As Long
jCount = 0

Dim spent As Long 'this is the "shoebox" where we'll be storing and calculating the spent # per Jon

Sheets("DWU_JON_Balance").Activate 'This section of code clears the previous Spent values and color coding
Range("E2:E300").Select
Selection.Interior.ColorIndex = 0
Selection.ClearContents
spent = 0

For jRow = 2 To jon.Range("A65536").End(xlUp).Row
For sRow = 2 To shop.Range("A65536").End(xlUp).Row
If shop.Cells(sRow, "B") = jon.Cells(jRow, "B").Value Then
sCount = sCount + 1
If shop.Cells(sRow, "P").Value = 1 Then 'Evaluate if JON matches
spent = spent + shop.Cells(sRow, "I").Value 'Add to spent shoebox
End If
jon.Cells(jRow, "E").Value = spent 'paste total spent into jon worksheet
jCount = jCount + 1 'This checks the next line of the jon worksheet
End If

Next sRow
Next jRow

End Sub

 

[ChrisOswald]

Hi,

Without having seen your spreadsheet, I can think of two possible reasons why you were getting nothing but zero's.

1) In the code, you were summing with sheet "Shop" column I instead of J. If you didn't have values in I, you'd get 0 back.

2) You have an If statement looking for a value of 1 in column P of the "Shop" sheet. If you don't have any values of 1, you'd never add anything to your 'spent' variable.

It also look's like there's a logic error: you never reset the 'spent' variable back to 0, so you'd end up placing a running total.

Also, Scount and Jcount never were used for anything, so I just removed them.

Option Explicit
Sub total_spent_jon()
'The purpose of this Macro is to sum the amont spent per shop for a JON on DWU_Shop
'and list that sum on DWU_Jon_Balance, and then calculate the Remaining.
    Dim shop                            As Worksheet
    Dim jon                             As Worksheet
    Dim sRow                            As Long
    Dim jRow                            As Long
    'use double or currency for dollar amounts
    Dim spent                           As Double
 
    Set shop = Sheets("DWU_Shop")
    Set jon = Sheets("DWU_JON_Balance")
    jon.Range("E2:E300").Interior.ColorIndex = 0
    jon.Range("E2:E300").ClearContents
    spent = 0
    For jRow = 2 To jon.Range("A65536").End(xlUp).Row
        For sRow = 2 To shop.Range("A65536").End(xlUp).Row
            If shop.Cells(sRow, "B") = jon.Cells(jRow, "B").Value Then
 
            'if col P isn't an actual controlling value, you (might) want to
            'remove the if/end if construction below
                If shop.Cells(sRow, "P").Value = 1 Then
 
                    'Original code had column I: the text describing the problem
                    'said J:  I used J
                    spent = spent + shop.Cells(sRow, "J").Value
                End If
 
            End If
        Next sRow
 
        'now that we've looked at every possible cell on the Shop sheet for
        'one given value on the JON sheet, we can output the total amounts...
        jon.Cells(jRow, "E").Value = spent
 
        'be sure to reset the total spent!
        spent = 0
    Next jRow
End Sub

Just out of curiousity, is there any reason you didn't want to use a sumifs() formula for this instead of a macro?

 

[Shushanna]

Hi Chris,

1) In the code, you were summing with sheet "Shop" column I instead of J. If you didn't have values in I, you'd get 0 back.

Good eye! I don't think that's the cause. I did that because I wondered if the fact the cells in J were equations was causing the 0's. Column I was just numbers. Using column I still gave me 0's, and I forgot to switch it back to J before I copy and pasted it here.

2) You have an If statement looking for a value of 1 in column P of the "Shop" sheet. If you don't have any values of 1, you'd never add anything to your 'spent' variable.

That's a good point. I'll take that out and see if it works.

It also look's like there's a logic error: you never reset the 'spent' variable back to 0, so you'd end up placing a running total.

Also, Scount and Jcount never were used for anything, so I just removed them.

I had originally defined it as spent = 0 in the top of the loop, but I took it out to see if it was somehow causing the 0's. Will definitely put it back in.

You're right, I should remove the counts.

Just out of curiousity, is there any reason you didn't want to use a sumifs() formula for this instead of a macro?

I've never used it before. Is that an excel function or a VBA macro? I definitely want the totals from the Shop tab to end up on the JON tab.

Thank you for your help, Chris!

 

[Shushanna]

Yay! I changed the code to this, and then it worked. Thank you Chris!

Sub total_spent_jon()
'The purpose of this Macro is to sum the amont spent per shop for a JON on DWU_Shop
'and list that sum on DWU_Jon_Balance, and then calculate the Remaining.
Dim shop As Worksheet 'This is the worksheet that lists totals spent per shop
Set shop = Sheets("DWU_Shop")
Dim jon As Worksheet 'This is the worksheet that lists the total budgetted per Jon
Set jon = Sheets("DWU_JON_Balance")
Dim sRow As Long 'row index on shop worksheet
Dim jRow As Long 'row index on jon worksheet
Dim spent As Long 'this is the "shoebox" where we'll be storing and calculating the spent # per Jon
'clearing any values from previous run
Sheets("DWU_JON_Balance").Activate 'This section of code clears the previous Spent values and color coding
Range("E2:E300").Select
Selection.Interior.ColorIndex = 0
Selection.ClearContents

For jRow = 2 To jon.Range("A65536").End(xlUp).Row
spent = 0
For sRow = 2 To shop.Range("A65536").End(xlUp).Row
'clearing previous values
If shop.Cells(sRow, "B") = jon.Cells(jRow, "B").Value Then
spent = spent + shop.Cells(sRow, "J").Value 'Add to spent shoebox
End If
jon.Cells(jRow, "E").Value = spent 'paste total spent into jon worksheet
'The $ remaining and % remaining are calculated in Excel\
Next sRow
Next jRow
End Sub

 

[ChrisOswald]

In regards to the SumIf() (or sumifs()) formula, here's a quick example of how it's used.

Excel Workbook
	A	B	C	D	E
1	Job Number	Total $		Job Number	Dollar
2	101	8		101	2
3	102	1		101	3
4	103	2		102	1
5	104	4		103	2
6	105	0		101	3
7				104	4
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=SUMIF(D$2:D$7,A2,E$2:E$7)
B3	=SUMIF(D$2:D$7,A3,E$2:E$7)
B4	=SUMIF(D$2:D$7,A4,E$2:E$7)
B5	=SUMIF(D$2:D$7,A5,E$2:E$7)
B6	=SUMIF(D$2:D$7,A6,E$2:E$7)

Which (outside of the cell references) seems to be exactly what you want without code. Syntax is =Sumif(Range I'm doing a logical test on, Logical test, range I want to sum over if test is true).

SumIfs() works like sumif, but you're allowed multiple criteria. Unfortunately it wasn't added until 2007, but looking at your working code it appears you only have one criteria to match on.

 

[Shushanna]

Neat! Thanks for the Sum if explaination, Chris.