none
need help with activecell (or something else) RRS feed

  • Question

  • Hello. I have a worksheet that is reporting the damage done by a technician. The tech number and name is in column B and C, respectively. After that from columns D thru H the damage information is filled in. The issue is that there could be 20 techs this month for this location but next month the same location could have 30 techs on the list. Which would be the same issue for each other location and the number of techs showing up on the list.

    So, I've been toying with the loop below that does provide the correct information in the specified cells.  And if you are wondering why the formula in vba like this, the reports get exported not only by me but the GM's as well.  The person before use to get calls from GM's and ..... enough said. 

    What I'm thinking is the Range("").Value needs to change so that the number of techs on the list doesn't become an issue.  Please note that the B10 is the cell where this all starts which is the tech id and C10 has the tech name.  Then D10 thru H10 is the information on the damage.  Then offset one row and start again until the active cell is empty.  Also notice that in the formula at the end it is referencing back to column B where the tech number is.  Even the referencing of IFERROR will not be correct.  The referencing for C5 will never change since that is the location that only appears in C5. 

    Eventually, I would like to put the information into a module but that is not crucial at this time.

    Can someone point me, nudge, or show me the direction I should be taking here with this?

    Thanks...John

        Range("B10").Select
       
        Do Until IsEmpty(ActiveCell)
           
            Range("D10").Value = [COUNTIFS(Damage!$B:$B,C5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!I:I,B10)]
            Range("E10").Value = [SUMIFS(Damage!$Y:$Y,Damage!$B:$B,C5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!I:I,B10)]   'amt claimed
            Range("F10").Value = [SUMIFS(Damage!$Z:$Z,Damage!$B:$B,C5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!I:I,B10)]   'amt approved
            Range("G10").Value = [IFERROR(E10-F10, 0)] 'difference (claimed-approved)
            Range("H10").Value = [IFERROR(G10/E10,0)]  'percent of savings (difference/amt claimed)

            ActiveCell.Offset(1, 0).Select
           
        Loop

    Monday, August 25, 2014 3:23 AM

Answers

  • Hi,

    If you want to improve the efficiency, you can edit the code to loop through every cell in the column B instead of using Range.Select method.

    I write a sample for your reference. In my sample, I loop through all the cells in column B and set the values of column D, E, F, G and H. You can edit based on your requirement.

    Sub test()
    
    Dim ws As Worksheet
    Dim celB As Range
    
    Set ws = ActiveWorkbook.Sheets("Damage")
    i = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    
    For Each celB In ws.Range("B2:B" & i)
    
        TechID = "$B" & celB.Row
        Claimed = "$E" & celB.Row
        Approved = "$F" & celB.Row
        Diff = "$G" & celB.Row
        
        celB.Offset(0, 2).Formula = "=COUNTIFS(Damage!$B:$B,C5,Damage!$D:$D,"">=1/1/2014"",Damage!$D:$D,""<=12/31/2014"",Damage!I:I," & TechID & ")"
        celB.Offset(0, 3).Formula = "=SUMIFS(Damage!$Y:$Y,Damage!$B:$B,C5,Damage!$D:$D,"">=1/1/2014"",Damage!$D:$D,""<=12/31/2014"",Damage!I:I," & TechID & ")"  'amt claimed
        celB.Offset(0, 4).Formula = "=SUMIFS(Damage!$Z:$Z,Damage!$B:$B,C5,Damage!$D:$D,"">=1/1/2014"",Damage!$D:$D,""<=12/31/2014"",Damage!I:I," & TechID & ")"  'amt approved
        celB.Offset(0, 5).Formula = "=IFERROR(" & Claimed & "-" & Approved & ", 0)" 'difference (claimed-approved)
        celB.Offset(0, 6).Formula = "=IFERROR(" & Diff & "/" & Claimed & ",0)" 'percent of savings (difference/amt claimed)
    
    
    Next
    
    End Sub



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 26, 2014 6:07 AM
    Moderator
  • Sorry.  I made a silly mistake in the macro I gave you.  (I was adapting it from a much simpler worksheet I used to test it.)  Replace these lines:

        Set LocationRange = Range("Damage!$B:$B")
        Set DateRange = Range("Damage!$D:$D")
        Set TechIDRange = Range("Damage!$I:$I")
        Set ClaimRange = Range("Damage!$Y:$Y")
        Set ApprovedRange = Range("Damage!$Z:$Z)
        Set curLocation = ActiveSheet.Range("$C$5")
    

    with these:

        Dim ws As Worksheet
    
        Set ws = ActiveWorkbook.Sheets("Sheet34") ' or whatever the "Damage" sheet is    
        Set LocationRange = ws.Range("$B:$B")
        Set DateRange = ws.Range("$D:$D")
        Set TechIDRange = ws.Range("$I:$I")
        Set ClaimRange = ws.Range("$Y:$Y")
        Set ApprovedRange = ws.Range("$Z:$Z)
        Set curLocation = ActiveSheet.Range("$C$5")

    Try that fix and let me know.

    • Marked as answer by johnboy0276 Wednesday, August 27, 2014 1:10 AM
    Tuesday, August 26, 2014 10:34 PM

All replies

  • Hi.

    I am not sure if you want the solution to use your shortcut evaluate technique (the square brackets) or if it could be a regular macro.  I'd go with a regular macro because I think it would be more efficient.

    But just to fix the sample code you provided...the problem you have is that all references used in Evaluate are assumed to be absolute unless they are named ranges.  So, if you select any column in row 10 and define the following named ranges:

    TechID = "$B10"
    Claimed = "$E10"
    Approved = "$F10"
    Diff = "$G10"

    then I think you could fix your code by using:

    Range("B10").Select
       
    Do Until IsEmpty(ActiveCell)
           
        ActiveCell.Offset(0,2).Value = COUNTIFS(Damage!$B:$B,C5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!I:I,TechID)]
        ActiveCell.Offset(0,3).Value = [SUMIFS(Damage!$Y:$Y,Damage!$B:$B,C5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!I:I,TechID)]   'amt claimed
        ActiveCell.Offset(0,4).Value = [SUMIFS(Damage!$Z:$Z,Damage!$B:$B,C5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!I:I,TechID)]   'amt approved
        ActiveCell.Offset(0,5).Value = [IFERROR(Claimed - Approved, 0)] 'difference (claimed-approved)
        ActiveCell.Offset(0,6).Value = [IFERROR(Diff / Claimed,0)]  'percent of savings (difference/amt claimed)
    
        ActiveCell.Offset(1, 0).Select
           
    Loop
    

    But this seems very inefficient and I'd suggest using a macro with a dynamic range (named or not) instead.  If you'd like to see something like that let me know.

    Monday, August 25, 2014 9:13 AM
  • Thank you for the reply.  Yes I would like to see a macro as how this would be done.  In the meantime I will see how the above work outs.

    Thanks again.

    ...John

    update:  I have given your suggestion above a try and yes, it is inefficient the way I have it. 


    • Edited by johnboy0276 Monday, August 25, 2014 5:28 PM
    Monday, August 25, 2014 12:27 PM
  • Here's a sample macro:

    Sub DamageReport()
        Dim LocationRange As Range
        Dim DateRange As Range
        Dim TechIDRange As Range
        Dim ClaimRange As Range
        Dim ApprovedRange As Range
        Dim techCount As Long
        Dim curRow As Range
        Dim curLocation As Range
        Dim tempClaimed As Double
        Dim tempApproved As Double
        Dim tempDiff As Double
        Dim tempSavings As Double
    
        Set LocationRange = Range("Damage!$B:$B")
        Set DateRange = Range("Damage!$D:$D")
        Set TechIDRange = Range("Damage!$I:$I")
        Set ClaimRange = Range("Damage!$Y:$Y")
        Set ApprovedRange = Range("Damage!$Z:$Z)
        Set curLocation = ActiveSheet.Range("$C$5")
    
        techCount = WorksheetFunction.CountA(ActiveSheet.Range("$B10:$B50") ' change this to whatever maximum range of techs might be
        
        Set TargetRange = Range("B10:H10").Resize(techCount) ' this could also be done with a dynamic named range
    
        For Each curRow In TargetRange.Rows
            curRow.Cells(3).Value = WorksheetFunction.CountIfs(LocationRange, curLocation, DateRange, ">=1/1/2014", DateRange, "<=12/31/2014", TechIDRange, curRow.Cells(1))
            tempClaimed = WorksheetFunction.SumIfs(ClaimRange, LocationRange, curLocation, DateRange, ">=1/1/2014", DateRange, "<=12/31/2014", TechIDRange, curRow.Cells(1))
            tempApproved = WorksheetFunction.SumIfs(ApprovedRange, LocationRange, curLocation, DateRange, ">=1/1/2014", DateRange, "<=12/31/2014", TechIDRange, curRow.Cells(1))
            tempDiff = tempClaimed - tempApproved
            tempSavings = 0
            if tempClaimed > 0 then
                tempSavings = tempDiff / tempClaimed
            end if
    
            curRow.Cells(4).Value = tempClaimed
            curRow.Cells(5).Value = tempApproved
            curRow.Cells(6).Value = tempDiff
            curRow.Cells(7).Value = tempSavings
        Next curRange
    End Sub
    

    Keep in mind this is pretty rough and could be improved on quite a bit but should give you the basic idea.  Also, if this report is one that can update dynamically (i.e. whenever you add more data to the "Damage" sheet instead of only when you want to compile/export the results), then you could enter the formulas right in the worksheet and use an "IF" statement to check if the ID column is empty first

    Tuesday, August 26, 2014 5:17 AM
  • Hi,

    If you want to improve the efficiency, you can edit the code to loop through every cell in the column B instead of using Range.Select method.

    I write a sample for your reference. In my sample, I loop through all the cells in column B and set the values of column D, E, F, G and H. You can edit based on your requirement.

    Sub test()
    
    Dim ws As Worksheet
    Dim celB As Range
    
    Set ws = ActiveWorkbook.Sheets("Damage")
    i = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    
    For Each celB In ws.Range("B2:B" & i)
    
        TechID = "$B" & celB.Row
        Claimed = "$E" & celB.Row
        Approved = "$F" & celB.Row
        Diff = "$G" & celB.Row
        
        celB.Offset(0, 2).Formula = "=COUNTIFS(Damage!$B:$B,C5,Damage!$D:$D,"">=1/1/2014"",Damage!$D:$D,""<=12/31/2014"",Damage!I:I," & TechID & ")"
        celB.Offset(0, 3).Formula = "=SUMIFS(Damage!$Y:$Y,Damage!$B:$B,C5,Damage!$D:$D,"">=1/1/2014"",Damage!$D:$D,""<=12/31/2014"",Damage!I:I," & TechID & ")"  'amt claimed
        celB.Offset(0, 4).Formula = "=SUMIFS(Damage!$Z:$Z,Damage!$B:$B,C5,Damage!$D:$D,"">=1/1/2014"",Damage!$D:$D,""<=12/31/2014"",Damage!I:I," & TechID & ")"  'amt approved
        celB.Offset(0, 5).Formula = "=IFERROR(" & Claimed & "-" & Approved & ", 0)" 'difference (claimed-approved)
        celB.Offset(0, 6).Formula = "=IFERROR(" & Diff & "/" & Claimed & ",0)" 'percent of savings (difference/amt claimed)
    
    
    Next
    
    End Sub



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 26, 2014 6:07 AM
    Moderator
  • Hello Luna and KMart92, thank you both for responding. 

    I tried both ways and the most success came from Luna's method.  KMart92, in trying your method I had problems in getting it past the Set lines.  I kept tweaking it all but to no avail. 

    Luna, I tweaked yours a bit and it all worked thru with no errors.  I show it below.  But, no numbers in the cells other than zero (0).  I placed the COUNTIFS and SUMIFS into cells as formulas with changing the TechID to a cell reference (B12, etc) and it worked as a formula.  If I replace 'TechID' at the end of each countifs and sumifs with the cell reference (B12) then it works as well.  I even tried to used celB.Offset(0, -2) in place of 'TechID' but still got zero (0) for all cells.  When I step thru it, hovering over the celB.Offset(#, #).Value it shows 0 (zero) while the TechID has the appropriate cell reference to it.

    This is now getting more puzzling than frustrating.  Any more thoughts on this?

    ... John

    Dim ws As Worksheet
    Dim celB As Range
    Set ws = ActiveWorkbook.Sheets("Sheet34")
    i = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    For Each celB In ws.Range("B12:B" & i)
        TechID = "$B" & celB.Row
        Claimed = "$E" & celB.Row
        Approved = "$F" & celB.Row
        Diff = "$G" & celB.Row
        ChargeBack = "$I" & celB.Row
       
        celB.Offset(0, 2).Value = [COUNTIFS(Damage!$B:$B,$C$5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!$I:$I,TechID)]   'claim count
        celB.Offset(0, 3).Value = [SUMIFS(Damage!$Y:$Y,Damage!$B:$B,$C$5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!$I:$I,TechID)]  'amt claimed
        celB.Offset(0, 4).Value = [SUMIFS(Damage!$Z:$Z,Damage!$B:$B,$C$5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!$I:$I,TechID)]  'amt approved
        celB.Offset(0, 5).Value = [IFERROR(Claimed - Approved, 0)] 'difference (claimed-approved)
        celB.Offset(0, 6).Value = [IFERROR(Diff / Claimed, 0)] 'percent of savings (difference/amt claimed)
        celB.Offset(0, 7).Value = [SUMIFS(Damage!$AA:$AA,Damage!$B:$B,$C$5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!$I:$I,TechID)]  'amt charged back
        celB.Offset(0, 8).Value = [IFERROR(Approved - ChargeBack, 0)] 'net amt paid
    Next


    Tuesday, August 26, 2014 3:36 PM
  • Sorry.  I made a silly mistake in the macro I gave you.  (I was adapting it from a much simpler worksheet I used to test it.)  Replace these lines:

        Set LocationRange = Range("Damage!$B:$B")
        Set DateRange = Range("Damage!$D:$D")
        Set TechIDRange = Range("Damage!$I:$I")
        Set ClaimRange = Range("Damage!$Y:$Y")
        Set ApprovedRange = Range("Damage!$Z:$Z)
        Set curLocation = ActiveSheet.Range("$C$5")
    

    with these:

        Dim ws As Worksheet
    
        Set ws = ActiveWorkbook.Sheets("Sheet34") ' or whatever the "Damage" sheet is    
        Set LocationRange = ws.Range("$B:$B")
        Set DateRange = ws.Range("$D:$D")
        Set TechIDRange = ws.Range("$I:$I")
        Set ClaimRange = ws.Range("$Y:$Y")
        Set ApprovedRange = ws.Range("$Z:$Z)
        Set curLocation = ActiveSheet.Range("$C$5")

    Try that fix and let me know.

    • Marked as answer by johnboy0276 Wednesday, August 27, 2014 1:10 AM
    Tuesday, August 26, 2014 10:34 PM
  • Kmart92, thank you for the follow up.  Below is a snippet of what I am getting with the new changes.  The tech number is column B row 12.  I did a break and stepped thru and hovered.  The curLocation shows the city name in cell C5.  The curRow shows the tech number you see below.  The range begins with B12 and I went to H200, which should cover the largest market.  Sheet34 is where the report is located.  Damage is the name of the sheet where all the data is located.  Then I relooked at your's and Luna's information and realized just how STUPID(!) I was.  I inadvertently, without thinking, changed the line Set ws = ActiveWorkbook.Sheets("Damage") to Sheet34 thinking activeworkbook and the sheet I was working on.  When it needed to be the sheet Damage (like you both had) where all the data is located.  I just rolled my eyes, changed from Sheet34 to Damage and all is fine.  Yours worked and Luna's worked.  How stupid of a reflex move is that?  An I didn't catch it until just now.  A good portion of today was frustrating over not getting this to work.  Maybe there was some good that came from the walk I took tonight, afterall. 

    My apologies go out to you and Luna for giving you both some bad feedback on your posts. 

    Thank you both for hangin' in there anyway. 

    ...John

    Wednesday, August 27, 2014 1:10 AM
  • Hi,

    >>I show it below.  But, no numbers in the cells other than zero (0).<<

    Sorry for my mistake, I ignored that you are setting the formula. I have edited my original code in the first reply to set the formula of Range instead of setting the value. Since I'm not sure about whether the formula is what you want, you could edit the content of the formula above by yourself if it is not correct.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 27, 2014 1:45 AM
    Moderator
  • Glad you got it working.  And as Luna's updated post shows, you could also use formulas right in the cells so the report would update dynamically as more damage data was supplied.  Something like:

    in cell D10, you could have:

    =IF(ISEMPTY($B10,"",COUNTIFS(Damage!$B:$B,$C$5,Damage!$D:$D,">=1/1/2014",Damage!$D:$D,"<=12/31/2014",Damage!I:I,$B10))

    and then copy that formula down for whatever the maximum number of TechIDs might be.  And you could do something similar for each cell just use the IF statement to make the cell blank if the ID cell is empty.

    But I thought you wanted a report that would only get updated when you manually ran the macro.

    • Marked as answer by johnboy0276 Wednesday, August 27, 2014 1:24 PM
    • Unmarked as answer by johnboy0276 Wednesday, August 27, 2014 1:24 PM
    Wednesday, August 27, 2014 3:18 AM
  • That is correct.  If I took what I had and used it as a formula on the sheet, it would work.  That is what I was getting at.  I was thinking there was something wrong with what I had.  The entire workbook is button driven as well as a userform.  Those who view this can also export a report (from a button) and then email just the exported report to someone.  That is why I do not want to have any formulas showing in the cells.  There are 9 sheets that is holding all the data that is used throughout the workbook.  The GM's, RD's, and others, think the one place for the needed reports is great.  Each month the data gets updated and it is ready to go.

    Thanks again....John

    Wednesday, August 27, 2014 1:33 PM