Solved

# VBA, VLOOKUP + SUMIF

Posted on 2013-05-21
Medium Priority
920 Views
I am using a vlookup in vba to return a value (below is a sample of the code).  I have been asked to remove a column of data so I now need to combine the sumif calcuation to the column with the  vlookup is this possible?

``````fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 16, False),"""")"
wsNew.Range("B13").Formula = fmla

wsNew.Range("B13").Value = wsNew.Range("B13").Value 'Professional Fees
``````

The SUMIF function is =SUMIF(\$C\$30:\$C\$45,"PF:*",\$H\$30:\$H\$45) + IF(SUM(SAMT)<>0,B13,0)
0
Question by:jmac001
• 6
• 6

LVL 81

Expert Comment

ID: 39185007
If your VLOOKUP returns an error, then IFERROR returns an empty string. Your SUMIF formula can't add the empty string--and will cause a run-time error.

There are two workarounds:
1. Change the empty string in the IFERROR to a 0
2. Wrap the SUMIF formula inside a SUM. SUM will ignore the empty string.

The first workaround is shown below:
``````Sub test()
Dim fmlaS As String, fmlaV As String, strLookupCell As String, strLookupRange As String, strLookupSheet As String, _
strPath As String, strFilename As String
Dim wsNew As Worksheet
fmlaV = "IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & _
strLookupRange & ", 16, False),0)"
fmlaS = "=SUMIF(\$C\$30:\$C\$45,""PF:*"",\$H\$30:\$H\$45) + IF(SUM(SAMT)<>0," & fmlaV & ",0)"

wsNew.Range("B13").Formula = fmlaS
wsNew.Range("B13").Value = wsNew.Range("B13").Value 'Professional Fees

End Sub
``````

And here is the second workaround:
``````Sub test()
Dim fmlaS As String, fmlaV As String, strLookupCell As String, strLookupRange As String, strLookupSheet As String, _
strPath As String, strFilename As String
Dim wsNew As Worksheet
fmlaV = "IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & _
strLookupRange & ", 16, False),"""")"
fmlaS = "=SUM(SUMIF(\$C\$30:\$C\$45,""PF:*"",\$H\$30:\$H\$45), IF(SUM(SAMT)<>0," & fmlaV & ",0))"

wsNew.Range("B13").Formula = fmlaS
wsNew.Range("B13").Value = wsNew.Range("B13").Value 'Professional Fees

End Sub
``````
0

Author Comment

ID: 39185503
Tried both solutions and neither worked.  Attaching a sample of the workbook.  You can reference the code in AddSheet module.  When the sheet is added it will populate data in the specified cells.  Cells B13:B18 have forecasted values in them,  once populated I need to be able to add any values that are in H30:H45 based on the value in category column C30:H45 (SUMIF(\$C\$30:\$C\$45,""PF:*"",\$H\$30:\$H\$45) + IF(SUM(SAMT)<>0,") .

Also need the range to be dynamic if possible, user has the option to add rows as needed.
Test-PCT-v2.xlsm
0

LVL 81

Expert Comment

ID: 39185718
I don't have a G drive full of data, so I can't really test the code other than to assure it compiles. I'll need you to test the code and report back. "Doesn't work" isn't descriptive enough. You'll need to tell me what parts work and what parts don't.

For test purposes, I changed your Addsheet module to the code below. First it puts the VLOOKUP formula in cell B13. Then it captures the value returned by the VLOOKUP and puts the SUMIF formula in that same cell (using the captured value from VLOOKUP). The formula is left in place so you can verify that it is correctly entered.

The code does not change any other cell, nor does it make any ranges dynamic.
``````Option Explicit

'Replace "Sheet1" with the name of the sheet to be copied.
Dim i As Integer, x As Integer, UpdateLinks As Integer
Dim shtname As String, strPath As String, strFilename As String, strLookupSheet As String, strLookupRange As String, _
strLookupCell As String, strLookupValue As String

Dim wbOutput As Workbook, wbLookup As Workbook
Dim wsTemplate As Worksheet, wsNew As Worksheet
Dim dbl As Double

Set wbOutput = ActiveWorkbook
shtname = InputBox("Enter Project Number?", "Sheet name?")

'check for valid shtName
On Error Resume Next
Set wsNew = wbOutput.Sheets(shtname)
If Err.Number = 9 Then
'there was NOT a sheet with this name. so OK
ActiveSheet.Unprotect
wbOutput.Sheets("Template").Copy _
Before:=wbOutput.Sheets("Summary")

Set wsNew = ActiveSheet ' the newly copied one
wsNew.Name = shtname

wsNew.Range("B7").Value = shtname

'ActiveSheet.Shapes("Button 10").Delete

Sheets("Template").Protect

strPath = "G:\Store Planning\Projects\Reports\"
strFilename = "Budget Info.xlsx"
strLookupSheet = "Cost Tracker Budget Info"
strLookupRange = "A1:U250"

strLookupCell = "B7"

Application.ScreenUpdating = False
Workbooks.Open strPath & strFilename

Dim fmla As String

'=VLOOKUP(B7,'G:\Store Planning\Projects\Reports\[Budget Info.xlsx]Cost Tracker Budget Info'!\$A\$1:\$u\$250, 4, FALSE)
fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 2, False),"""")"
wsNew.Range("B4").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 4, False),"""")"
wsNew.Range("B5").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 5, False),"""")"
wsNew.Range("B6").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 6, False),"""")"
wsNew.Range("B8").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 7, False),"""")"
wsNew.Range("B9").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 8, False),"""")"
wsNew.Range("B10").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 9, False),"""")"
wsNew.Range("E10").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 10, False),"""")"
wsNew.Range("I4").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 11, False),"""")"
wsNew.Range("I5").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 12, False),"""")"
wsNew.Range("I6").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 13, False),"""")"
wsNew.Range("I7").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 14, False),"""")"
wsNew.Range("I8").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 15, False),"""")"
wsNew.Range("L5").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 16, False),0)"
wsNew.Range("B13").Formula = fmla
dbl = wsNew.Range("B13").Value
fmla = "=SUMIF(\$C\$30:\$C\$45,""PF:*"",\$H\$30:\$H\$45) + IF(SUM(SAMT)<>0," & dbl & ",0)"
wsNew.Range("B13").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 17, False),0)"
wsNew.Range("B14").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 18, False),0)"
wsNew.Range("B15").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 19, False),0)"
wsNew.Range("B16").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 20, False),0)"
wsNew.Range("B17").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 21, False),0)"
wsNew.Range("B18").Formula = fmla

wsNew.Range("B4").Value = wsNew.Range("B4").Value 'Brand
wsNew.Range("B5").Value = wsNew.Range("B5").Value 'PM
wsNew.Range("B6").Value = wsNew.Range("B6").Value 'Store Name
wsNew.Range("B8").Value = wsNew.Range("B8").Value 'PCR
wsNew.Range("B9").Value = wsNew.Range("B9").Value 'Country
wsNew.Range("B10").Value = wsNew.Range("B10").Value 'Sales
wsNew.Range("E10").Value = wsNew.Range("E10").Value 'Seanon
wsNew.Range("I4").Value = wsNew.Range("I4").Value 'Design Type
wsNew.Range("I5").Value = wsNew.Range("I5").Value 'Scope Type
wsNew.Range("I6").Value = wsNew.Range("I6").Value 'Gross SQ
wsNew.Range("I7").Value = wsNew.Range("I7").Value ' Selling SF
wsNew.Range("I8").Value = wsNew.Range("I8").Value ' Frontage
wsNew.Range("L5").Value = wsNew.Range("L5").Value ' Open Date
'wsNew.Range("B13").Value = wsNew.Range("B13").Value 'Professional Fees
wsNew.Range("B14").Value = wsNew.Range("B14").Value 'Parts
wsNew.Range("B15").Value = wsNew.Range("B15").Value 'Freight & Taxes
wsNew.Range("B16").Value = wsNew.Range("B16").Value 'Contract
wsNew.Range("B17").Value = wsNew.Range("B17").Value 'Other Cost
wsNew.Range("B18").Value = wsNew.Range("B18").Value 'Contingency

Workbooks(strFilename).Close savechanges:=False
'Application.ScreenUpdating = True
ActiveSheet.Protect
End If
End Sub
``````
0

Author Comment

ID: 39185802
Still not seeing the excepted result.  If you look at the tab 1510030 where B13 is \$17,800 and in  row 30 PF:Arch Fees is \$200 after that has been inputted the excepted result in B13 is \$18,000.
0

LVL 81

Expert Comment

ID: 39185858
Remember, I can't see the formula in tab 1510030.

Please step through the code as it is putting formulas in cell B13. Does the VLOOKUP return the correct value? Does the SUMIF return the correct value?

To step through the code, click in the light gray margin to the left of these statements. Should should see a large maroon dot. When the code hits that dot, you'll be thrown into the debugger. You can then F8 to advance one statement at a time or F5 to continue to the next maroon dot (or end of macro).
``````            fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 16, False),0)"
wsNew.Range("B13").Formula = fmla
dbl = wsNew.Range("B13").Value
fmla = "=SUMIF(\$C\$30:\$C\$45,""PF:*"",\$H\$30:\$H\$45) + IF(SUM(SAMT)<>0," & dbl & ",0)"
wsNew.Range("B13").Formula = fmla
``````
0

Author Comment

ID: 39185893
Sorry, I see the excepted results from the vlookup, I dont see the value from the SUMIF once it is inputted.  Step through the code and it did not error.
0

LVL 81

Expert Comment

ID: 39185916
The SUMIF formula is working at my end--it returns \$200 for store 1510030. I assume that the VLOOKUP is returning \$17,800.

Is the test for SUM(SAMT)<>0 on hidden cells H48:H62 being passed on the real data? It isn't passed on the worksheet for 1510030 that you posted. Is the wrong range being tested?

0

Author Comment

ID: 39187423

I apologize it should have been for FAMT, I adjusted the code that you sent for FAMT instead of SAMT and it still does not show up in B13.  Question, since at the time the data populated (vlookup) there is no data in H30:H45, should the code be attached to the sheet being created?  The user may or may not add any additional information to the area, but if they do it needs to be added so that the cost is incremental.
0

LVL 81

Expert Comment

ID: 39189651
I don't know why you are testing the sum of range FAMT to decide whether to add the value returned by the VLOOKUP. Why not just add it?
``````Sub AddSheet()
'Replace "Sheet1" with the name of the sheet to be copied.
Dim i As Integer, x As Integer, UpdateLinks As Integer
Dim shtname As String, strPath As String, strFilename As String, strLookupSheet As String, strLookupRange As String, _
strLookupCell As String, strLookupValue As String

Dim wbOutput As Workbook, wbLookup As Workbook
Dim wsTemplate As Worksheet, wsNew As Worksheet
Dim dbl As Double

Set wbOutput = ActiveWorkbook
shtname = InputBox("Enter Project Number?", "Sheet name?")

'check for valid shtName
On Error Resume Next
Set wsNew = wbOutput.Sheets(shtname)
If Err.Number = 9 Then
'there was NOT a sheet with this name. so OK
ActiveSheet.Unprotect
wbOutput.Sheets("Template").Copy _
Before:=wbOutput.Sheets("Summary")

Set wsNew = ActiveSheet ' the newly copied one
wsNew.Name = shtname

wsNew.Range("B7").Value = shtname

'ActiveSheet.Shapes("Button 10").Delete

Sheets("Template").Protect

strPath = "G:\Store Planning\Projects\Reports\"
strFilename = "Budget Info.xlsx"
strLookupSheet = "Cost Tracker Budget Info"
strLookupRange = "A1:U250"

strLookupCell = "B7"

Application.ScreenUpdating = False
Workbooks.Open strPath & strFilename

Dim fmla As String

'=VLOOKUP(B7,'G:\Store Planning\Projects\Reports\[Budget Info.xlsx]Cost Tracker Budget Info'!\$A\$1:\$u\$250, 4, FALSE)
fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 2, False),"""")"
wsNew.Range("B4").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 4, False),"""")"
wsNew.Range("B5").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 5, False),"""")"
wsNew.Range("B6").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 6, False),"""")"
wsNew.Range("B8").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 7, False),"""")"
wsNew.Range("B9").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 8, False),"""")"
wsNew.Range("B10").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 9, False),"""")"
wsNew.Range("E10").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 10, False),"""")"
wsNew.Range("I4").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 11, False),"""")"
wsNew.Range("I5").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 12, False),"""")"
wsNew.Range("I6").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 13, False),"""")"
wsNew.Range("I7").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 14, False),"""")"
wsNew.Range("I8").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 15, False),"""")"
wsNew.Range("L5").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 16, False),0)"
wsNew.Range("B13").Formula = fmla
dbl = wsNew.Range("B13").Value
fmla = "=SUMIF(\$C\$30:\$C\$45,""PF:*"",\$H\$30:\$H\$45)" & IIf(dbl = 0, "", " + " & dbl)
wsNew.Range("B13").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 17, False),0)"
wsNew.Range("B14").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 18, False),0)"
wsNew.Range("B15").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 19, False),0)"
wsNew.Range("B16").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 20, False),0)"
wsNew.Range("B17").Formula = fmla

fmla = "=IFERROR(VLOOKUP(" & strLookupCell & ",'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & strLookupRange & ", 21, False),0)"
wsNew.Range("B18").Formula = fmla

wsNew.Range("B4").Value = wsNew.Range("B4").Value 'Brand
wsNew.Range("B5").Value = wsNew.Range("B5").Value 'PM
wsNew.Range("B6").Value = wsNew.Range("B6").Value 'Store Name
wsNew.Range("B8").Value = wsNew.Range("B8").Value 'PCR
wsNew.Range("B9").Value = wsNew.Range("B9").Value 'Country
wsNew.Range("B10").Value = wsNew.Range("B10").Value 'Sales
wsNew.Range("E10").Value = wsNew.Range("E10").Value 'Seanon
wsNew.Range("I4").Value = wsNew.Range("I4").Value 'Design Type
wsNew.Range("I5").Value = wsNew.Range("I5").Value 'Scope Type
wsNew.Range("I6").Value = wsNew.Range("I6").Value 'Gross SQ
wsNew.Range("I7").Value = wsNew.Range("I7").Value ' Selling SF
wsNew.Range("I8").Value = wsNew.Range("I8").Value ' Frontage
wsNew.Range("L5").Value = wsNew.Range("L5").Value ' Open Date
'wsNew.Range("B13").Value = wsNew.Range("B13").Value 'Professional Fees
wsNew.Range("B14").Value = wsNew.Range("B14").Value 'Parts
wsNew.Range("B15").Value = wsNew.Range("B15").Value 'Freight & Taxes
wsNew.Range("B16").Value = wsNew.Range("B16").Value 'Contract
wsNew.Range("B17").Value = wsNew.Range("B17").Value 'Other Cost
wsNew.Range("B18").Value = wsNew.Range("B18").Value 'Contingency

Workbooks(strFilename).Close savechanges:=False
'Application.ScreenUpdating = True
ActiveSheet.Protect
End If
End Sub
``````
0

Author Comment

ID: 39191218

The goal is to add the 2 values together. I originally had the cost from the vlookup going into a separate column called Base.  The Base and Feasibility column have been combined so once the Base is created in the sheet I it needs to add any additional cost that may be added to the Feasibility Driver area on the worksheet.  I tried
fmla = "=SUMIF(\$C\$30:\$C\$45,""PF:*"",\$H\$30:\$H\$45)" & IIf(dbl = 0, "", " + " & dbl)
wsNew.Range("B13").Formula = fmla

and the values that placed in H30 still did not add to the value in B13.

Adding the spreadsheet the I am testing and I also added the vlookup if this will help.
Test-PCT-v2.xlsm
0

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 39191275
I don't know when the values are populated in C30:C44 and H30:H44, but it is certainly after the AddSheet macro runs. If you wipe out the SUMIF formula at the end of the AddSheet macro, then B13 will never include any values from H30:H44.

In the most recent macro I posted, I commented out statement 122 that wiped out that formula. The workbook you posted was still wiping out the formula. When you tested my code, did you do so with the formula in place or not?
0

Author Comment

ID: 39195364
I did miss the commented out line.  Updated code and it is now working thank you, thank you, thank you so much for patience.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacā¦
###### Suggested Courses
Course of the Month14 days, 19 hours left to enroll