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.

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

<tbody>
</tbody>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

DatePeriod (24 hour)Price 1DatePeriod (48 periods)Price 2
01-Jan-171 32.2201-Jan-171 32.22
01-Jan-172 32.2201-Jan-172 32.22
01-Jan-174 30.7201-Jan-173 30.72
01-Jan-176 30.9101-Jan-174 30.72
01-Jan-178 25.9201-Jan-175 30.91
01-Jan-1710 25.9201-Jan-176 30.91
01-Jan-1712 30.9901-Jan-177 25.92
01-Jan-1714 31.8201-Jan-178 25.92
01-Jan-1716 38.0601-Jan-179 25.92
01-Jan-1718 40.5501-Jan-1710 25.92
01-Jan-1720 45.1401-Jan-1711 30.99
01-Jan-1722 52.0501-Jan-1712 30.99
01-Jan-1724 49.9201-Jan-1713 31.82
01-Jan-1726 46.9701-Jan-1714 31.82
01-Jan-1728 43.8001-Jan-1715 38.06
01-Jan-1730 42.5401-Jan-1716 38.06
01-Jan-1732 45.2501-Jan-1717 40.55
01-Jan-1734 53.5601-Jan-1718 40.55
01-Jan-1736 67.5701-Jan-1719 45.14
01-Jan-1738 61.2701-Jan-1720 45.14
01-Jan-1740 44.9401-Jan-1721 52.05
01-Jan-1742 41.8601-Jan-1722 52.05
01-Jan-1744 35.9901-Jan-1723 49.92
01-Jan-1746 31.9701-Jan-1724 49.92
01-Jan-1748 30.9201-Jan-1725 46.97
01-Jan-1726 46.97
01-Jan-1727 43.80
01-Jan-1728 43.80
01-Jan-1729 42.54
01-Jan-1730 42.54
01-Jan-1731 45.25
01-Jan-1732 45.25
01-Jan-1733 53.56
01-Jan-1734 53.56
01-Jan-1735 67.57
01-Jan-1736 67.57
01-Jan-1737 61.27
01-Jan-1738 61.27
01-Jan-1739 44.94
01-Jan-1740 44.94
01-Jan-1741 41.86
01-Jan-1742 41.86
01-Jan-1743 35.99
01-Jan-1744 35.99
01-Jan-1745 31.97
01-Jan-1746 31.97
01-Jan-1747 30.92
01-Jan-17

<tbody>
</tbody>
 
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
lookup on calculated value not exact in list
Replies
5
Views
789
starkc69
S

Forum statistics

Threads
1,214,593
Messages
6,120,434
Members
448,961
Latest member
nzskater

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