From A column of sorted dates, and B Column of Costs,
This code works until I try to distribute Costs by Month, starting in column D.
Sub MonthCosts()
Dim LastRow As Long
Dim rDate As Range
Dim dtMax As Date, dtMin As Date
Dim startdate As Date, enddate As Date
LastRow = Cells(65536, 1).End(xlUp).Row
x = 4
Set rDate = Range("B4:B" & LastRow)
'Find Start & End Dates
dtMax = Application.Max(rDate)
dtMin = Application.Min(rDate)
Range("B2").Activate
ActiveCell.Value = dtMin
Range("C2").Activate
ActiveCell.Value = dtMax
startdate = Range("B2").Value
enddate = Range("C2").Value
startdate = DateSerial(Year(dtMin), Month(dtMin), 1)
enddate = DateSerial(Year(dtMax), Month(dtMax), 1)
enddate = DateAdd("d", -1, DateAdd("m", 1, enddate))
'Fill Calendar Headers
Do
With Cells(3, x)
.Value = startdate
.NumberFormat = "yymm"
End With
startdate = DateAdd("m", 1, startdate)
x = x + 1
Loop Until startdate > enddate
' Problem starts in this section to distribute Month Cost. I am lost here
Dim CalArray As Variant
CalArray = Range("B4:C" & LastRow).Value
'For CalMonth = 1 To x
'Do Until ActiveCell.Value = ""
'ReDim Preserve .................
'...
'
'ActiveCell.Offset(1, 0).Select
'Loop
'Paste YearMonth and Cost Array in Calendar Columns
End Sub
In my provided Excel file it will be more clear what I want...
As I purchase an Item the cost increases 10%. I need the formula that will show the combined total cost of quantity purchased.
Thanks in advance
Dan
I have a 10 mb file, with about 40,000 lines of data (Excel 2003), and in the Product # column, all of our products have a three letter vendor ID,
exa: BNX416-022
I want to insert a column and have the vendor name appear next to the product,
exa: BNX416-022 Binax
I could type in the names, and pull down (copy) each product line, but was wondering if I could use the IF or LOOKUP function to make it faster.
There are about 40 vendors representing the 40,000 product ID's
As always, any help is appreciated.
Hi
I attached a workbook. Basically, i need to readjust the formula for forecast cost from March 2010 to Dec 2010 (Range D3:M5) so that the total cost (Range N3:N5) will be the same as total budget cost (Range N11:N13).
The different between total forecast cost and total budget cost is because some cost in Jan & Feb 2010 were not taken up in Actual. Now, i need to add those not taken up amount in Jan & Feb 2010 to March - Dec 2010 by smooth it out so that total cost will be equal to total budget cost.
I hope you understand what i am trying to say.
Thanks
Hi
Please help me with a formula that will look at a column with % figures and then return a Rating.
1. Column A = Has % Numbers ( eg: 23.67%, 33.87% etc )
2. Column B = Formula that will look at column A and return a Rating / Value
20% = 1
21% = 1.1
22% = 1.2
23% = 1.3
and so on upto :
60%= 5
Note : The % sometimes is 20.45% and not rounded off to 20%.
Please can you help
Vaughan
Hi,
I'm seeking your great help on the below issue
I've different rating matrix as below;
more than 90% - " Excellent"
80% to 89% - "Very Good"
65% to 79% - "Good"
below 64% - "Low"
hence if any of the rating falls in a cell need a formula to corresponds that (lets say in cell "A1" if the rating is 85% then result should be "Very Good")
Thanks
