incremental sum with range that varies

izzy117

New Member
Joined
Feb 13, 2008
Messages
41
I am trying to sum a sales forecast and highlight the month when my inventory will run out. I have months as headers with numbers in each row and the last row being my current inventory. Given the sample below i would want to highlight the cells in column D (mar-11) because the sum of Jan+Feb+Mar is > Inventory.

As stated in the title the range will vary starting in Feb-11. I am able to locate the column of the current month with the following
mycolumn = Cells.Find(What:=(WorksheetFunction.Text(Now, "mmm-yy")), After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
I take no offence in being informed of how amateur that line is :laugh:

Part No. .....jan-11...feb-11...mar-11....apr-11...inventory
Item 1..........100......300.......400.........100.......775
Item 2..........300......500.......500.........400.......1,000


I am trying to avoid making a ton of if statements like...
if sum(mycolumn, mycolumn.offset(0, 1)) > Range("F2").Value then
do mycolumn.offset(0, 1) highlight
if sum(mycolumn, mycolumn.offset(0, 2)) > Range("F2").Value then
do mycolumn.offset(0, 2) highlight

I am sure this will require a loop but i've never exicute one like this before.
Any help would be appreciated.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
EDIT: Does the conditional formatting add a lot to the file size? Say if i were to just leave it on 1000 rows?



simple answer: The range of ALL the work varies wildly.

I'm running a macro to copy and paste customer informationn onto the sheet with all their information as sampled above. Then do a loop to gather bill of materials information for each of those part numbers and then pull out unique rows of materials and sumif the columns for those given materials. Depending on how many different part numbers there are my work can start on A:20 or A:400.
 
Last edited:
Upvote 0
Users do not want me to dump last months numbers. They needs to see them all year long.

next month (Feb-11) i want to sum(Feb+Mar+Apr) and finding that the sum is greater than inventory, highlight apr-11.
Part No. .....jan-11...feb-11...mar-11....apr-11...inventory
Item 1..........100......300.......400.........100.......775

In 2 months (Mar-11) i want to sum(Mar+Apr) and finding the sum is less than inventory, highlight nothing.
Part No. .....jan-11...feb-11...mar-11....apr-11...inventory
Item 1..........100......300.......400.........100.......775
 
Upvote 0
This is what i am using. Is there a smarter way of doing this?

Sub SumOfMonthsGoingForward()
'ignoring data from previous months
Dim mycurrentmonth As Range
myinventory = ActiveCell.Offset(0, 47).Value
mycolumn = Cells.Find(What:=(WorksheetFunction.Text(Now, "mmm-yy")), After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column - 2
Set mycurrentmonth = ActiveCell.Offset(0, mycolumn)
If mycurrentmonth.Value > myinventory Then
mycurrentmonth.Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 1))) > myinventory Then
mycurrentmonth.Offset(0, 1).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 2))) > myinventory Then
mycurrentmonth.Offset(0, 2).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 3))) > myinventory Then
mycurrentmonth.Offset(0, 3).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 4))) > myinventory Then
mycurrentmonth.Offset(0, 4).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 5))) > myinventory Then
mycurrentmonth.Offset(0, 5).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 6))) > myinventory Then
mycurrentmonth.Offset(0, 6).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 7))) > myinventory Then
mycurrentmonth.Offset(0, 7).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 8))) > myinventory Then
mycurrentmonth.Offset(0, 8).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 9))) > myinventory Then
mycurrentmonth.Offset(0, 9).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 10))) > myinventory Then
mycurrentmonth.Offset(0, 10).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 11))) > myinventory Then
mycurrentmonth.Offset(0, 11).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 12))) > myinventory Then
mycurrentmonth.Offset(0, 12).Select
GoTo 1
Else
If Application.Sum(Range(mycurrentmonth, mycurrentmonth.Offset(0, 13))) > myinventory Then
mycurrentmonth.Offset(0, 13).Select
GoTo 1
Else
Exit Sub
1:
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599963377788629
.PatternTintAndShade = 0
End With
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top