CODEWITHSUNDEEP
vbaexcel
Jason Wingate
Jason Wingate

Reputation: 13

Finding the sum of values in one workbook based on a single criteria in another workbook

I've been writing a code that uses 3 workbooks - but I am having issues with the final output.

wb1 opens up wb2 and wb3, cross-references (using VLOOKUP) the weights in wb3, copies them over to the corresponding customer address in wb2, then multiples the weights by the quantity ordered in wb2's address line.

The entire code works as I planned, except for the final output. wb2 now has the final weights in column Q.

Here is the final output code (it returns no values at the moment), with the entire code posted below for reference.

'Enter in the weights data into the final sheet
tempCount = 0
lastCount = lastRow1

For tempCount = 1 To lastCount
    Set lookFor = wb1.Sheets(1).Cells(tempCount + 1, 11) ' value to find
    Set lookForRange = wb2.Sheets(1).Range("$C$2:$C$" & lastRow2)   'Range of values to lookup
    Set srchRange = wb2.Sheets(1).Range("$Q$7:$Q$" & lastRow2) 

wb1.Sheets(1).Activate
    ActiveSheet.Cells(tempCount + 1, 12).Value = Application.WorksheetFuction.SumIf(lookForRange, lookFor, srchRange)
        On Error Resume Next

Next

Below is the entire code for reference.

'Define workbooks
Dim wb2FileName As Variant
Dim wb3FileName As Variant
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook

'Count last rows in columns
Dim lastRow1 As Long
Dim lastRow2 As Long
Dim lastRow3 As Long

'Variables
Dim lookFor As Range
Dim lookForRange As Range
Dim srchRange As Range
Dim tempCount As Integer
Dim lastCount As Integer


'Open up all workbooks to work on
 Set wb1 = ThisWorkbook

wb2FileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.csv;*.xls;*.xlsx;*.xlsm),*.csv;*.xls;*.xlsx;*.xlsm", Title:="Customer Order Data Worksheet", MultiSelect:=False)
If wb2FileName <> False Then
    Workbooks.Open Filename:=wb2FileName
End If
    Set wb2 = Workbooks.Open(wb2FileName)

wb3FileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.csv;*.xls;*.xlsx;*.xlsm),*.csv;*.xls;*.xlsx;*.xlsm", Title:="Source Reference File (Weights)", MultiSelect:=False)
If wb3FileName <> False Then
    Workbooks.Open Filename:=wb3FileName
End If
    Set wb3 = Workbooks.Open(wb3FileName)

'Find the last row in the customer data workbook and the source weights workbook
wb2.Sheets(1).Activate
    lastRow2 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

wb3.Sheets(1).Activate
    lastRow3 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

'Use VLOOKUP to enter in weights from the reference sheet into the customer order data sheet, then multiply by the quantity
tempCount = 0
lastCount = lastRow2

For tempCount = 1 To lastCount
    Set lookFor = wb2.Sheets(1).Cells(tempCount + 6, 10) ' value to find
    Set srchRange = wb3.Sheets(1).Range("$B$2:$C$" & lastRow3)    'source

    wb2.Sheets(1).Activate
        ActiveSheet.Cells(tempCount + 6, 16).Value = Application.WorksheetFunction.VLookup(lookFor, srchRange, 2, False)
        ActiveSheet.Cells(tempCount + 6, 17).Value = ActiveSheet.Cells(tempCount + 6, 11).Value * ActiveSheet.Cells(tempCount + 6, 16).Value
            On Error Resume Next
Next

'Delete top 5 rows from the final sheet and insert new header
wb1.Sheets(1).Activate
    ActiveSheet.Rows("1:5").Delete
    ActiveSheet.Cells(1, 12).Value = "Weights"

'Find the last row on the final sheet
lastRow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

'Enter in the weights data into the final sheet
tempCount = 0
lastCount = lastRow1

For tempCount = 1 To lastCount
    Set lookFor = wb1.Sheets(1).Cells(tempCount + 1, 11) ' value to find
    Set lookForRange = wb2.Sheets(1).Range("$C$2:$C$" & lastRow2)   'Range of values to lookup
    Set srchRange = wb2.Sheets(1).Range("$Q$7:$Q$" & lastRow2) 


    wb1.Sheets(1).Activate
         ActiveSheet.Cells(tempCount + 1, 12).Value = Application.WorksheetFuction.SumIf(lookForRange, lookFor, srchRange)


Next

Upvotes: 1

Views: 92

Answers (1)

dwirony
dwirony

Reputation: 5450

Okay, I made several additions/changes to your code, so bear with me.

  1. I added Option Explicit to the top of your module (you might already have it but you didn't include your Sub/End Sub so we couldn't tell).

  2. Got rid of Activate & ActiveSheet. This just leads to a plethora of possible errors and a loss in readability. Use explicit references instead.

  3. You need a way to Exit Sub if one of your wb2 or wb3 return False. If they do they'll just throw an error. Now you'll get a MsgBox and the subroutine will exit appropriately.

  4. Got rid of On Error Resume Next. You shouldn't need that here. If you have to use it, at least turn errors back on by using On Error GoTo 0 soon after.

  5. Moved some Sets inside their corrresponding If statements, and moved a couple static Sets outside of a loop (if it's always the same, why put it inside the loop?).

Now, for your issue with the SumIf - I believe you're encountering this issue because your criteria range and your sum range are not the same size. When they aren't, you can get a return of 0 because they don't line up properly. I've changed Range("$Q$7:$Q$" & lastRow2) to Range("$Q$2:$Q$" & lastRow2) in hopes that fixes that (but you might need to change Range("$C$2:$C$" & lastRow2) to Range("$C$7:$C$" & lastRow2) if that's your intended range.

Hope this helps!

Option Explicit
Sub Test()
'Define workbooks
Dim wb2FileName As Variant, wb3FileName As Variant
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook

'Count last rows in columns
Dim lastRow1 As Long, lastRow2 As Long, lastRow3 As Long

'Variables
Dim lookFor As Range, lookForRange As Range, srchRange As Range
Dim tempCount As Integer, lastCount As Integer

'Open up all workbooks to work on
Set wb1 = ThisWorkbook

wb2FileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.csv;*.xls;*.xlsx;*.xlsm),*.csv;*.xls;*.xlsx;*.xlsm", Title:="Customer Order Data Worksheet", MultiSelect:=False)

If wb2FileName <> False Then
    Set wb2 = Workbooks.Open(wb2FileName)
Else
    MsgBox "No wb2, exiting"
    Exit Sub
End If

wb3FileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.csv;*.xls;*.xlsx;*.xlsm),*.csv;*.xls;*.xlsx;*.xlsm", Title:="Source Reference File (Weights)", MultiSelect:=False)

If wb3FileName <> False Then
    Set wb3 = Workbooks.Open(wb3FileName)
Else
    MsgBox "No wb3, exiting"
    Exit Sub
End If

'Find the last row in the customer data workbook and the source weights workbook
lastRow2 = wb2.Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row
lastRow3 = wb3.Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row

'Use VLOOKUP to enter in weights from the reference sheet into the customer order data sheet, then multiply by the quantity
lastCount = lastRow2

For tempCount = 1 To lastCount
    Set lookFor = wb2.Sheets(1).Cells(tempCount + 6, 10) ' value to find
    Set srchRange = wb3.Sheets(1).Range("$B$2:$C$" & lastRow3)    'source

    wb2.Sheets(1).Cells(tempCount + 6, 16).Value = Application.WorksheetFunction.VLookup(lookFor, srchRange, 2, False)
    wb2.Sheets(1).Cells(tempCount + 6, 17).Value = wb2.Sheets(1).Cells(tempCount + 6, 11).Value * wb2.Sheets(1).Cells(tempCount + 6, 16).Value
Next

'Delete top 5 rows from the final sheet and insert new header
wb1.Sheets(1).Rows("1:5").Delete
wb1.Sheets(1).Cells(1, 12).Value = "Weights"

'Find the last row on the final sheet
lastRow1 = wb1.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

'Enter in the weights data into the final sheet
lastCount = lastRow1

Set lookForRange = wb2.Sheets(1).Range("$C$2:$C$" & lastRow2)   'Range of values to lookup
Set srchRange = wb2.Sheets(1).Range("$Q$2:$Q$" & lastRow2)

For tempCount = 1 To lastCount

    Set lookFor = wb1.Sheets(1).Cells(tempCount + 1, 11) ' value to find

    wb1.Sheets(1).Cells(tempCount + 1, 12).Value = Application.WorksheetFuction.SumIf(lookForRange, lookFor, srchRange)

Next

End Sub

Upvotes: 1

Related Questions