Forum Discussion
Populating cells with a Date across 12 TABS
- look at both as decimal (or percentage) are they the same or off by 100? It looks like the rate schedule is a true decimal number (i.e. 0.00088 or 0.088%) while the other number is coming in as % units (i.e. 0.088 which if formatted as % would be 8.8%) so basically you need to convert from % UNIT to decimal value.
=(ROUND(K6,3)= ROUND(100*K$3,3))
or BETTER yet change the formula in K3 to be =100*... so
=100*XLOOKUP(LEFT($I5,3),LEFT('Rate Schedule'!$B5:$B16,3),'Rate Schedule'!C5:C16,"error")
In case you cannot see the macro for the Overview Tab here it is:
Sub FillOverview()
Dim w1 As Worksheet ' month sheet
Dim w2 As Worksheet ' month overview sheet
Dim r As Long ' source row number
Dim m As Long ' last row number
Dim s As Long ' target row number
Dim c As Long ' target column number
Dim v1 As Double ' current usage
Dim v2 As Double ' previous usage
If Len(ActiveSheet.Name) <> 3 Then
MsgBox "Please select a month sheet, then try again!", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Set w1 = ActiveSheet
m = w1.Range("A" & w1.Rows.Count).End(xlUp).Row
Set w2 = w1.Next.Next
w2.Range("B5:ZZ355").ClearContents
For r = 6 To m
' Electric
v1 = w1.Range("H" & r).Value
v2 = w1.Range("BI" & r).Value
Select Case v1
Case 0
c = 2
Case Is > v2 * 1.5
c = 9
Case Is > v2 * 1.4
c = 8
Case Is > v2 * 1.3
c = 7
Case Is > v2 * 1.2
c = 6
Case Is > v2 * 1.1
c = 5
Case Is > v2
c = 4
Case v2
c = 3
Case Is < v2 * 0.5
c = 15
Case Is < v2 * 0.6
c = 14
Case Is < v2 * 0.7
c = 13
Case Is < v2 * 0.8
c = 12
Case Is < v2 * 0.9
c = 11
Case Is < v2
c = 10
End Select
s = w2.Cells(w2.Rows.Count, c).End(xlUp).Row + 1
If s < 5 Then s = 5
w2.Cells(s, c).Value = w1.Range("A" & r).Value
' Gas
v1 = w1.Range("X" & r).Value
v2 = w1.Range("BX" & r).Value
Select Case v1
Case 0
c = 2
Case Is > v2 * 1.5
c = 9
Case Is > v2 * 1.4
c = 8
Case Is > v2 * 1.3
c = 7
Case Is > v2 * 1.2
c = 6
Case Is > v2 * 1.1
c = 5
Case Is > v2
c = 4
Case v2
c = 3
Case Is < v2 * 0.5
c = 15
Case Is < v2 * 0.6
c = 14
Case Is < v2 * 0.7
c = 13
Case Is < v2 * 0.8
c = 12
Case Is < v2 * 0.9
c = 11
Case Is < v2
c = 10
End Select
c = c + 15
s = w2.Cells(w2.Rows.Count, c).End(xlUp).Row + 1
If s < 5 Then s = 5
w2.Cells(s, c).Value = w1.Range("A" & r).Value
' Water
v1 = w1.Range("AG" & r).Value
v2 = w1.Range("CG" & r).Value
Select Case v1
Case 0
c = 2
Case Is > v2 * 1.5
c = 9
Case Is > v2 * 1.4
c = 8
Case Is > v2 * 1.3
c = 7
Case Is > v2 * 1.2
c = 6
Case Is > v2 * 1.1
c = 5
Case Is > v2
c = 4
Case v2
c = 3
Case Is < v2 * 0.5
c = 15
Case Is < v2 * 0.6
c = 14
Case Is < v2 * 0.7
c = 13
Case Is < v2 * 0.8
c = 12
Case Is < v2 * 0.9
c = 11
Case Is < v2
c = 10
End Select
c = c + 30
s = w2.Cells(w2.Rows.Count, c).End(xlUp).Row + 1
If s < 5 Then s = 5
w2.Cells(s, c).Value = w1.Range("A" & r).Value
Next r
Application.ScreenUpdating = True
End Sub
Because I have added columns and maybe even some rows since this macro was somewhat working, I'm sure References will have to be changed or added. As always, any changes or if there is a better way to accomplish what I've I got, I am wide open to it.
Carl
Carl_61 I updated the macro to a) use a function for that repetitive SELECT CASE for column selection and then b) created a loop for the utilities so then what I did in a) probably isn't even needed lol.
In anycase I also added 2 variables for the columns on the sheet to use for each utility this month and prior month col. I also added the percent value to the building name.
the icon you mentioned isn't there so i ran it using VIEW > MACROS > FillOverview
It ran REALLY slow. Did it always run that slow? Maybe it was just my computer.
BTW, the setting of the location of the Overview tab was using ".NEXT.NEXT" meaning basically 2 tabs over from the active tab which was NOT correct. I change it to be only ".NEXT" but I'm really not a fan of the ".NEXT" to define the location of a Tab as it is prone to problems if someone moves tabs around (as you can see it caused an issue here).
- I can appreciate your concern but those are your numbers. When I do spot checks the calculations seem correct. Please do spot checks yourself and see if there is something wrong. BTW, the Water usage from the previous month looks way off. I have no idea how these values are copied into this workbook/worksheet (for Jan). I see the future months use formulas to 'pull' the prior month data.
Finally you asked if "high percentages be due to lack of prior month USAGE numbers" and that is NO because a lack of prior month number would be a 0 and cause division by 0 error which I catch and display as LM=0 (Last Month = 0) and you can see some of those also. - Matt, I have to wonder about the percentages for the overview. Would these super high percentages be due to the lack of prior month USAGE numbers or due to a formula issue? I am seeing 1300+% numbers that don't make any sense. Or maybe they do. I just was not expecting to see 200+ percentage numbers. The numbers I was expecting to see are numbers that would cause them to fall into the columns they are in based on a comparison between the previous usage and the current. To fall in line with what the column headers are showing. Its just hard to fathom there could be a 600% or otherwise difference in usage between a previous and current month.
- Matt, I have to wonder about the percentages for the overview. Would these super high percentages be due to the lack of prior month USAGE numbers or due to a formula issue? I am seeing 1300+% numbers that don't make any sense. Or maybe they do. I just was not expecting to see 200+ percentage numbers. The numbers I was expecting to see are numbers that would cause them to fall into the columns they are in based on a comparison between the previous usage and the current. To fall in line with what the column headers are showing. Its just hard to fathom there could be a 600% or otherwise difference in usage between a previous and current month.
- I had no idea of the .NEXT.NEXT So I would have had no idea of what you are speaking of. As always if there is a better way I am fully up to it. And YES, it has always run slow. Have no idea why. I was hoping for better performance but I have no idea how to change that myself. Hopefully the changes you made will keep the data within their lanes. For some reason it use to create its own column just tot he right, outside the defined columns. I have not checked yet but was wondering if you were able to get the %# included with the building number? I guess I'll find out soon otherwise.