VBA code to copy an hourly value into two half hourly values

W

Woofy_McWoof_Woof

Board Regular
Joined
Oct 7, 2016
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Hi

I currently have a dataset which displays an hourly price each day over multiple years, I then have an index match formula that splits the hourly price into two half hourly prices (same value). Although this works it is taking too long to recalculate each time I update it and has greatly increased the memory size of the file. Is there anyway that rather than have the formula I just run a macro that converts the data for me?

I've copied min an example below, any help would be most appreciated.

[TABLE="width: 498"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Period (24 hour)
[/TD]
[TD]Price 1
[/TD]
[TD]Date
[/TD]
[TD]Period (48 periods)
[/TD]
[TD]Price 2
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]1
[/TD]
[TD] 32.22
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]1
[/TD]
[TD] 32.22
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]2
[/TD]
[TD] 32.22
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]2
[/TD]
[TD] 32.22
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]4
[/TD]
[TD] 30.72
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]3
[/TD]
[TD] 30.72
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]6
[/TD]
[TD] 30.91
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]4
[/TD]
[TD] 30.72
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]8
[/TD]
[TD] 25.92
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]5
[/TD]
[TD] 30.91
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]10
[/TD]
[TD] 25.92
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]6
[/TD]
[TD] 30.91
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]12
[/TD]
[TD] 30.99
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]7
[/TD]
[TD] 25.92
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]14
[/TD]
[TD] 31.82
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]8
[/TD]
[TD] 25.92
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]16
[/TD]
[TD] 38.06
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]9
[/TD]
[TD] 25.92
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]18
[/TD]
[TD] 40.55
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]10
[/TD]
[TD] 25.92
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]20
[/TD]
[TD] 45.14
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]11
[/TD]
[TD] 30.99
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]22
[/TD]
[TD] 52.05
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]12
[/TD]
[TD] 30.99
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]24
[/TD]
[TD] 49.92
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]13
[/TD]
[TD] 31.82
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]26
[/TD]
[TD] 46.97
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]14
[/TD]
[TD] 31.82
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]28
[/TD]
[TD] 43.80
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]15
[/TD]
[TD] 38.06
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]30
[/TD]
[TD] 42.54
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]16
[/TD]
[TD] 38.06
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]32
[/TD]
[TD] 45.25
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]17
[/TD]
[TD] 40.55
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]34
[/TD]
[TD] 53.56
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]18
[/TD]
[TD] 40.55
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]36
[/TD]
[TD] 67.57
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]19
[/TD]
[TD] 45.14
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]38
[/TD]
[TD] 61.27
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]20
[/TD]
[TD] 45.14
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]40
[/TD]
[TD] 44.94
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]21
[/TD]
[TD] 52.05
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]42
[/TD]
[TD] 41.86
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]22
[/TD]
[TD] 52.05
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]44
[/TD]
[TD] 35.99
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]23
[/TD]
[TD] 49.92
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]46
[/TD]
[TD] 31.97
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]24
[/TD]
[TD] 49.92
[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17
[/TD]
[TD]48
[/TD]
[TD] 30.92
[/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]25
[/TD]
[TD] 46.97
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]26
[/TD]
[TD] 46.97
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]27
[/TD]
[TD] 43.80
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]28
[/TD]
[TD] 43.80
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]29
[/TD]
[TD] 42.54
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]30
[/TD]
[TD] 42.54
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]31
[/TD]
[TD] 45.25
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]32
[/TD]
[TD] 45.25
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]33
[/TD]
[TD] 53.56
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]34
[/TD]
[TD] 53.56
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]35
[/TD]
[TD] 67.57
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]36
[/TD]
[TD] 67.57
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]37
[/TD]
[TD] 61.27
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]38
[/TD]
[TD] 61.27
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]39
[/TD]
[TD] 44.94
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]40
[/TD]
[TD] 44.94
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]41
[/TD]
[TD] 41.86
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]42
[/TD]
[TD] 41.86
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]43
[/TD]
[TD] 35.99
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]44
[/TD]
[TD] 35.99
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]45
[/TD]
[TD] 31.97
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]46
[/TD]
[TD] 31.97
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]47
[/TD]
[TD] 30.92
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17
[/TD]
[TD="align: right"]48
[/TD]
[TD] 30.92
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
VBA / macro to copy value into two cells

Hi

I thought I would resubmit this as unfortunately I had no responses. I hope someone maybe able to help as its driving me nuts :)

I currently have a dataset which displays an hourly price each day over multiple years, I then have an index match formula that splits the hourly price into two half hourly prices (same value). Although this works it is taking too long to recalculate each time I update it and has greatly increased the memory size of the file. Is there anyway that rather than have the formula I just run a macro that converts the data for me?

I've copied in an example below, any help would be most appreciated.

[TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Period (24 hour)[/TD]
[TD]Price 1[/TD]
[TD]Date[/TD]
[TD]Period (48 periods)[/TD]
[TD]Price 2[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]1[/TD]
[TD] 32.22[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]1[/TD]
[TD] 32.22[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]2[/TD]
[TD] 32.22[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]2[/TD]
[TD] 32.22[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]4[/TD]
[TD] 30.72[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]3[/TD]
[TD] 30.72[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]6[/TD]
[TD] 30.91[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]4[/TD]
[TD] 30.72[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]8[/TD]
[TD] 25.92[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]5[/TD]
[TD] 30.91[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]10[/TD]
[TD] 25.92[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]6[/TD]
[TD] 30.91[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]12[/TD]
[TD] 30.99[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]7[/TD]
[TD] 25.92[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]14[/TD]
[TD] 31.82[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]8[/TD]
[TD] 25.92[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]16[/TD]
[TD] 38.06[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]9[/TD]
[TD] 25.92[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]18[/TD]
[TD] 40.55[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]10[/TD]
[TD] 25.92[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]20[/TD]
[TD] 45.14[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]11[/TD]
[TD] 30.99[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]22[/TD]
[TD] 52.05[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]12[/TD]
[TD] 30.99[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]24[/TD]
[TD] 49.92[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]13[/TD]
[TD] 31.82[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]26[/TD]
[TD] 46.97[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]14[/TD]
[TD] 31.82[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]28[/TD]
[TD] 43.80[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]15[/TD]
[TD] 38.06[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]30[/TD]
[TD] 42.54[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]16[/TD]
[TD] 38.06[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]32[/TD]
[TD] 45.25[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]17[/TD]
[TD] 40.55[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]34[/TD]
[TD] 53.56[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]18[/TD]
[TD] 40.55[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]36[/TD]
[TD] 67.57[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]19[/TD]
[TD] 45.14[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]38[/TD]
[TD] 61.27[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]20[/TD]
[TD] 45.14[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]40[/TD]
[TD] 44.94[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]21[/TD]
[TD] 52.05[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]42[/TD]
[TD] 41.86[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]22[/TD]
[TD] 52.05[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]44[/TD]
[TD] 35.99[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]23[/TD]
[TD] 49.92[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]46[/TD]
[TD] 31.97[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]24[/TD]
[TD] 49.92[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-17[/TD]
[TD]48[/TD]
[TD] 30.92[/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]25[/TD]
[TD] 46.97[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]26[/TD]
[TD] 46.97[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]27[/TD]
[TD] 43.80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]28[/TD]
[TD] 43.80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]29[/TD]
[TD] 42.54[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]30[/TD]
[TD] 42.54[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]31[/TD]
[TD] 45.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]32[/TD]
[TD] 45.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]33[/TD]
[TD] 53.56[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]34[/TD]
[TD] 53.56[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]35[/TD]
[TD] 67.57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]36[/TD]
[TD] 67.57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]37[/TD]
[TD] 61.27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]38[/TD]
[TD] 61.27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]39[/TD]
[TD] 44.94[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]40[/TD]
[TD] 44.94[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]41[/TD]
[TD] 41.86[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]42[/TD]
[TD] 41.86[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]43[/TD]
[TD] 35.99[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]44[/TD]
[TD] 35.99[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]45[/TD]
[TD] 31.97[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]46[/TD]
[TD] 31.97[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[TD="align: right"]47[/TD]
[TD] 30.92[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Jan-17[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: VBA / macro to copy value into two cells

I have merged your two threads. Please refer to the Forum Rules (12) and the Forum Use Guidelines (7) to see why & how best to deal with a lack of response to your questions.


Assuming that your original data starts in cell A1 and results are to go in to columns D:F, then try this in a copy of your workbook.

Code: 
Sub Split_Periods()
  Dim a As Variant, b As Variant
  Dim i As Long
  
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To 2 * UBound(a), 1 To 3)
  For i = 1 To UBound(a)
    b(i * 2 - 1, 1) = a(i, 1): b(i * 2 - 1, 2) = i * 2 - 1: b(i * 2 - 1, 3) = a(i, 3)
    b(i * 2, 1) = a(i, 1): b(i * 2, 2) = i * 2: b(i * 2, 3) = a(i, 3)
  Next i
  Range("D2").Resize(UBound(b), 3).Value = b
  Range("D1:F1").Value = Array("Date", "Period (48 periods)", "Price 2")
End Sub
 
Upvote 0
Re: VBA / macro to copy value into two cells

Thank you Peter, that worked a treat. Apologies for reposting without reading the rules and guidelines, I shall endeavour to do this in future. :)
 
Upvote 0
Re: VBA / macro to copy value into two cells

Peter, I have just noticed that the period numbers continue on. These would need to be reset to 1 - 48 for each day. Is that possible?
 
Upvote 0
Re: VBA / macro to copy value into two cells

I'm struggling a bit with your sample data as you said hourly per day but there appears to be 25 values for 1-Jan and I don't understand the period numbers in your second column. never-the-less, see how this goes.

Code: 
Sub Split_Periods_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, p As Long
  
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To 2 * UBound(a), 1 To 3)
  For i = 1 To UBound(a)
    b(i * 2 - 1, 1) = a(i, 1): b(i * 2 - 1, 2) = i * 2 - 1 - p: b(i * 2 - 1, 3) = a(i, 3)
    b(i * 2, 1) = a(i, 1): b(i * 2, 2) = i * 2 - p: b(i * 2, 3) = a(i, 3)
    If i Mod 24 = 0 Then p = i * 2
  Next i
  Range("D2").Resize(UBound(b), 3).Value = b
  Range("D1:F1").Value = Array("Date", "Period (48 periods)", "Price 2")
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
lookup on calculated value not exact in list
Replies
5
Views
925
starkc69
S

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

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