Want to add new rows depending on a variable, and increment cell contents by 1

[ValFed]

Right, so basically I want to average daily prices of a commodity for a giving pricing period.

However, the period changes.

What I'm envisioning is as such: I tell excel the START DATE on cell B3, END DATE on B4, and B5 is "=B4-B3+1" and that gives the number of days (counting inclusively, hence the +1). So for example, for a pricing period of Sep10-13, that's 4 days, so B5 = 4.

Now, using the same example, I want a macro that adds values in the B column, starting from B7, as such:

Sep-10
Sep-11
Sep-12
Sep-13

So basically it's +1 day all the time until it reaches the END DATE (or until it increments to the value in B5 MINUS 1 --- that should be the cap.... i.e. notice how it's +0, then +1, +2 and finally +3).



Should the period be changed to Sep12-13, for example, excel would automatically remove the last 2 rows and their contents, and starting from B7 only the following would be shown:

Sep-12
Sep-13

(and the value in B5 would be 2).




Does that make sense?





I've been trying to do this but I am getting nowhere... I am sure one of you guys can help me out?

Thanks!!





After that is all done, I would manually input all the price indexes for each day on column C (starting from C7).

The average is calculate on cell D3. The formula is basically the sum of all the rows with price indexes (not sure how to get this to automatically expand or contract, so I'm using the entire C column) and then divides it by =COUNTA of the entire C column (counts everything that isn't blank). If you know of a cleaner/better way to do this, expanding and contracting the C column as it goes, that'd be nice, otherwise this is fine. I'm more worried about the above problem (inserting rows and incrementing).



Thanks a lot peeps!!

[ValFed]

Sorry guys I didn't realise I can't edit posts - I forgot to say that this is for MS EXCEL obviously (in Office 2010).

Thanks peeps

[Zvoni]

Code for Change-Event of the Worksheet-Object.
You'll note that i don't use the Value in B5

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim Difference As Long
 
    If (Target.Row = 3 And Target.Column = 2) Or (Target.Row = 4 And Target.Column = 2) Then
    
        Difference = DateDiff("d", CDate(Me.Cells(3, 2)), CDate(Me.Cells(4, 2)))
        
        If Difference >= 0 Then
        
            For i = 0 To Difference
            
                Me.Cells(i + 7, 2) = DateAdd("d", i, Me.Cells(3, 2))
            
            Next
        
        End If
    
    End If
 
End Sub

[ValFed]

Oh wow, thanks a lot Zvoni, it works!

As I suspected, the code is much simpler than what I was trying to do.



Could I ask you if it's possible to make one small addition? Right now, if the range given is 5 days, it will correctly add 5 date values along 5 rows staring from B7. If this is later changed to a SHORTER period, e.g 3 days only, it will also correctly do it, however the other 2 rows (the values in B10 and B11) will not be cleared and show as blank - instead they will have the "leftover" values from the previous period.

I'm thinking that the best way to do this is to run a script that before any new calculation, it'll clear the entire column B of ANY values, starting from B7. Can you add that to the script?

Thanks a lot!!

[Zvoni]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Static Difference As Long     '##### Changed from Dim to Static
 
    If (Target.Row = 3 And Target.Column = 2) Or (Target.Row = 4 And Target.Column = 2) Then
        '#### Additional Loop to clear the cells
        For i = 0 To Difference
        
            Me.Cells(i + 7, 2) = ""
        
        Next
        
        Difference = DateDiff("d", CDate(Me.Cells(3, 2)), CDate(Me.Cells(4, 2)))
        
        If Difference >= 0 Then
        
            For i = 0 To Difference
            
                Me.Cells(i + 7, 2) = DateAdd("d", i, Me.Cells(3, 2))
            
            Next
        
        End If
    
    End If
 
End Sub

[ValFed]

Thanks man it works wonderfully, you rock!! THANKS