Answered by:
need help with activecell (or something else)
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(E10F10, 0)] 'difference (claimedapproved)
Range("H10").Value = [IFERROR(G10/E10,0)] 'percent of savings (difference/amt claimed)ActiveCell.Offset(1, 0).Select
Loop
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 (claimedapproved) 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. Marked as answer by johnboy0276 Wednesday, August 27, 2014 1:11 AM
 Edited by Luna Zhang  MSFTModerator Wednesday, August 27, 2014 1:39 AM

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
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 (claimedapproved) 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.

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

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

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 (claimedapproved) 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. Marked as answer by johnboy0276 Wednesday, August 27, 2014 1:11 AM
 Edited by Luna Zhang  MSFTModerator Wednesday, August 27, 2014 1:39 AM

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 (claimedapproved)
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 
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

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

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

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