Solved

VBA, VLOOKUP + SUMIF

Posted on 2013-05-21
12
775 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 81

Expert Comment

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

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
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

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

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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

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

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

Open in new window

0
 

Author Comment

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

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

Brad
0
 

Author Comment

by:jmac001
ID: 39187423
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 81

Expert Comment

by:byundt
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
        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
ID: 39191218
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 81

Accepted Solution

by:
byundt earned 500 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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cost allcocation ... 10 23
extract a substring between backslashes from paths 10 22
Excel VBA 30 43
vba delte many shapes with same name 4 12
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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 two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

827 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