Stuck with appending a value

[twoplustwo]

Hi guys,

I have a calculation in a routine that I need to append to another worksheet. I've searched but I don't think I need the paste function?

I cannot for the life of me figure it out

Thanks a lot.

 

[shades]

Howdy. Check out PasteSpecial Values
________
RA106

 

[twoplustwo]

Hi Shades,

This will work in Excel VB?

 

[twoplustwo]

I'll attach what I have so far.

Basically, I work in Demand Forecasting. For some sites we reforecast a fair amount so as we move closer to real time we can trade excess/buy in according to their requirements.

The attached file/start of the code is looking to calculate the difference on between each subsequent forecast made after the initial one for that settlement day. I'm trying to then paste this value to "Differences".

Any help/advice would be awesome.

Thanks.

 

[chergh]

Hi I had some trouble understanding your current code so I just started from the start. Try using this code and tell me if it produces what you want.

Option Explicit


Sub ForecastChanges()

Dim rPeriodVol As Range
Dim rWeekday As Range
Dim rResiduals As Range
Dim rday As Range
Dim wsVolumeChanges As Worksheet
Dim wsForecasts As Worksheet
Dim wsSummary As Worksheet
Dim wb As Workbook
Dim rdiff As Variant
Dim lngRow As Long
Dim lngrow1 As Long
Dim i As Long

'set objects

Set wb = ThisWorkbook

Set wsVolumeChanges = wb.Worksheets("Differences")
Set wsSummary = wb.Worksheets("Summary")
Set wsForecasts = wb.Worksheets("Data")

lngRow = wsForecasts.Range("A1").End(xlDown).Row

If lngRow > 2 Then
    
    For i = 1 To lngRow - 2
    
        wsForecasts.Range("I" & i + 2).Value = Abs(wsForecasts.Range("D" & i + 2) - wsForecasts.Range("D" & i + 1))
    
    Next i
    
    lngrow1 = wsForecasts.Range("D3").End(xlDown).Row
    
    For i = 1 To lngrow1 - 1
    
       wsVolumeChanges.Range("A" & i + 1).Value = wsForecasts.Range("A" & i + 1).Value
       wsVolumeChanges.Range("B" & i + 1).Value = wsForecasts.Range("D" & i + 1).Value
    
    Next i
    
End If




End Sub

 

[twoplustwo]

Hi Che,

Thanks for the response.

I don't need to copy/paste the values as such - I need to perform this calc: When Weekday > Weekday -1 then set the residual for that period to 0 as this represent no forecast change.

When Weekday = Weekday +1 then work out the difference. This needs appending to "Differences".

This is what I've tried here.

For Each rP_Vol In Range("D2", Selection.End(xlDown)).Select 'Start calcs
                      Range("D2", Selection.End(xlToRight)).Select
                              
    If rWeekday > rWeekday.Offset(-1, 0) Then  'Identify new day/fc and set pVol to 0
     rResiduals = 0
    End If
   
    If rWeekday = rWeekday.Offset(-1, 0) Then 'Calcs vol when current day > last
     rResiduals = Abs(rP_Vol - rP_Vol.Offset(-1, 0)) / 1000 'MWh
    End If

I'll attached the whole spreadsheet.

I really appreciate your help dude.

 

[twoplustwo]

Che,

I am also trying to calculate this period by period rather than summing the entire range. My hope is that "Differences" will include the calcs mentioned above.

Once again, I'm really greatful for you helping me out

 

[twoplustwo]

Che,

You have the calcs right, but we append the wrong row for some reason! i.e. the originals rather than the residual.

We also need to set the 1st forecast of each day to 0 - otherwise we subtract forecasts for the previous day which is incorrect.

I owe you a pint

 

[twoplustwo]

Cool cool.

In the Settlement Date column we have the date for a certain day. In Forecast date we have the submission date for each forecast.

So...

SETTLEMENT_DATE DAYNUM FORECAST_DATE Period 1
01-Nov-07 5 31-Oct-07 18317
01-Nov-07 5 31-Oct-07 17993
01-Nov-07 5 01-Nov-07 17993
01-Nov-07 5 01-Nov-07 17993

Here, for the 1st Nov 2007 4 forecast changes were made in total for the 1st period in that day. From 18317 -> 17993 -> 17993 -> 17993. I need to calculate the differences between each reforecast for each day for all 48 period in each settlement day. Using this table, that'd be (324 + 0 + 0 + 0)

I think we need DayNum in there because we need to differentiate the first forecast in each day i.e. we can't subtract a forecast for the next settlement date from the last one from the day before. To this end, each residual for the initial forecast needs to be "0" in the Differences sheet.

I hope that's a bit better

 

[chergh]

Not sure I fully understand what your are doing but take a look at this:

Option Explicit


Sub ForecastChanges()

Dim rPeriodVol As Range
Dim rWeekday As Range
Dim rResiduals As Range
Dim rday As Range
Dim wsVolumeChanges As Worksheet
Dim wsForecasts As Worksheet
Dim wsSummary As Worksheet
Dim wb As Workbook
Dim rdiff As Variant
Dim lngRow As Long
Dim lngrow1 As Long
Dim i As Long

'set objects

Set wb = ThisWorkbook

Set wsVolumeChanges = wb.Worksheets("Differences")
Set wsSummary = wb.Worksheets("Summary")
Set wsForecasts = wb.Worksheets("Data")

lngRow = wsForecasts.Range("A1").End(xlDown).Row

If lngRow > 2 Then
    
For i = 3 To lngRow

    If wsForecasts.Range("A" & i).Value = wsForecasts.Range("A" & i - 1).Value Then
    
        wsVolumeChanges.Range("A" & i - 1).Value = wsForecasts.Range("A" & i).Value
        wsVolumeChanges.Range("B" & i - 1).Value = Abs(wsForecasts.Range("C" & i) - wsForecasts.Range("C" & i - 1)) / 1000
    
    Else
        wsVolumeChanges.Range("A" & i - 1).Value = wsForecasts.Range("A" & i).Value
        wsVolumeChanges.Range("B" & i - 1).Value = 0
    
    End If
    
Next i
    
End If




End Sub

 

[twoplustwo]

I basically need the same layout in Differences with the volume changes from forecast to forecast. Using the example I posted the first cell in Differences after the date for the 1st should be 324kWh, then 0 then 0 then 0 for the remainder. This is because we alter the first forecast from 18317kWh to 17993kWh, then any subsequent forecasts are 17993kWh. Thus, the differences should look like

SettDate ForecastDate Change
01-Nov-07 31-Oct-07 0
01-Nov-07 31-Oct-07 324
01-Nov-07 01-Nov-07 0
01-Nov-07 01-Nov-07 0

This needs to be for all periods in the spreadsheet I included.

Thanks mate.

Steve.

 

[chergh]

try this:

Option Explicit


Sub ForecastChanges()

Dim rPeriodVol As Range
Dim rWeekday As Range
Dim rResiduals As Range
Dim rday As Range
Dim wsVolumeChanges As Worksheet
Dim wsForecasts As Worksheet
Dim wsSummary As Worksheet
Dim wb As Workbook
Dim rdiff As Variant
Dim lngRow As Long
Dim lngrow1 As Long
Dim lngcol As Long
Dim i As Long
Dim j As Long

'set objects

Set wb = ThisWorkbook

Set wsVolumeChanges = wb.Worksheets("Differences")
Set wsSummary = wb.Worksheets("Summary")
Set wsForecasts = wb.Worksheets("Data")


lngRow = wsForecasts.Range("A1").End(xlDown).Row
lngcol = wsForecasts.Range("A1").End(xlToRight).Column

If lngRow > 2 Then
    
    
    
For j = 3 To lngcol
For i = 3 To lngRow

    If wsForecasts.Cells(i, 1).Value = wsForecasts.Cells(i - 1, 1).Value Then
        
        If j = 3 Then
            wsVolumeChanges.Cells(i - 1, 1).Value = wsForecasts.Cells(i, 1).Value
        End If
        
        wsVolumeChanges.Cells(i - 1, j - 1).Value = Abs(wsForecasts.Cells(i, j) - wsForecasts.Cells(i - 1, j)) / 1000
    
    Else
        If j = 3 Then
            wsVolumeChanges.Cells(i - 1, 1).Value = wsForecasts.Cells(i, 1).Value
        End If
        
        wsVolumeChanges.Cells(i - 1, j - 1).Value = 0
    
    End If
    
Next i
Next j

    
End If




End Sub

 

[twoplustwo]

Hi Che,

So close

What's going on with the second column in Differences? We have some really long decimals from somewhere. Also the dates don't seem to be pasting to Diff.

I really really appreciate your help mate.

 

[chergh]

I've edited the code so that the dates copy correctly, when I run the code i get a maximum of 3 decimal places. How long are your decimals?

 

[twoplustwo]

01-Nov-07 0.000351944 0.324

But to 4dp.

After the third number it's all cool.

 

[chergh]

In the spreadsheet you provided I dont get the value 0.000351944 anywhere on the spreadsheet after running the code.

 

[twoplustwo]

Eh I've gotten rid of it.

You've done me a massive favour there Chergh, thanks dude.

Would you mind taking a minute just to explain to me your method?

(I mean, I understand it all just wanted to see where I fell down)

Thanks again,

Steve.

 

[chergh]

I just tried to understand what you were wanting to do then thinking about the simplest way to do it, which in this case for me was stepping through each row in each column.

Essentially I figure out a way to do what needs to be done for one cell then I set about developing that method to work for the whole set of data.

 

[twoplustwo]

Hi guys (Che!),

I have another update. It appears as though the code isn't quite working as we need.

In the Differences tab we do not need to subtract first new forecast for a site from the last forecast for the site before it.

i.e.

11-Feb-08 ~00071160 11-Feb-08 43988
11-Feb-08 ~00071163 06-Feb-07 10000

Here we have two sites denoted by the key, and the forecast dates. For the last row we JUST need to include the initial volume in each period rather than setting it to 0.

If anyone would be willing to help it'd be top.

Thanks,

Steve.