Solved

VBA, VLOOKUP + SUMIF

Posted on 2013-05-21
12
737 Views
Last Modified: 2013-05-24
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

Open in new window


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

Expert Comment

by:byundt
Comment Utility
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

Open in new window


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

Open in new window

0
 

Author Comment

by:jmac001
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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

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
        Application.AskToUpdateLinks = False
        UpdateLinks = 3
        
        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

Open in new window

0
 

Author Comment

by:jmac001
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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

Open in new window

0
 

Author Comment

by:jmac001
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 80

Expert Comment

by:byundt
Comment Utility
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?

Brad
0
 

Author Comment

by:jmac001
Comment Utility
Brad,

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 80

Expert Comment

by:byundt
Comment Utility
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
        Application.AskToUpdateLinks = False
        UpdateLinks = 3
        
        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

Open in new window

0
 

Author Comment

by:jmac001
Comment Utility
Brad,

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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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

by:jmac001
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now