Thread: Macro to Find In Between Date/Time Values and use Text to Prompt Calculations

Hi Excel experts

I'm reaching out to you guys because I'm struggling to write 2 macro's although the 2nd macro uses the same exact process as the 1st, just the formula's are inverted. Any assistance from you guru's would be really really appreciated.

Below is the detailed description of the logic for both macro's, but like I said if you understand the 1st one, the 2nd one is just a couple quick modifications.

Macro 1

Step 1) Set Condition

ROW 6 will be used to check for “BUY” or “SELL”, and I will always start in cell J6.

Step 2) Find Range for Calculation

Move up to J3 and J4 which contains a date/time in each cell. Find all values in between and inclusive of those values in Column A, and the next 30 values in Column A after the J4 time will also be used.
The columns that will be used in the calculations will either be Colum C if it’s a “SELL” or Column D if it’s a “BUY”. For example, if A726:A844 is the range found, cells D726:D844 would be used if J6 showed “BUY”, or cells C726:C844 would be used if J6 showed “SELL”.

Step 3) Calculation with Formula’s

If J6 shows “BUY”

From Column D first cell in the found range, move over to Column J and this is where the data will be input.
The calculation will always be:
=(First cell in Column D of the found range – the Opening Price in correspondent column) * Factor in Cell J9. Copy this formula down until last value found in the range is calculated. The last 30 calculated values in J need to be Bolded/Italicized, or have some type of easily recognizable formatting.

My attached sheet includes the formula, cell referencing, and formatting for clarification.

Then after all the values in J are calculated move up to J6, move to the right 2 columns so we’re in L6 and check if it’s a “BUY” or “SELL”.

If J6 shows “SELL”

From Column C first cell in the found range, move over to Column J and this is where the data will be input.
The calculation will always be:
=(-First Cell in Column C of the found range + Opening Price in correspondent column) * Factor in Cell J9. Copy this formula down until last value found in the range is calculated. The last 30 calculated values in J need to be Bolded/Italicized, or have some type of easily recognizable formatting.
My attached sheet includes the formula, cell referencing, and formatting for clarification located in L826 as J6 did not show a “SELL”.

Then if J6 did show a “SELL” and all values have been calculated, move up to J6, move to the right 2 columns so we’re in L6 and check if it’s a “BUY” or “SELL”.

Repeat this process until there are no more “BUY” or “SELL” in row 6.

Macro 2

Step 1) Set Condition

ROW 6 will be used to check for “BUY” or “SELL”, and I will always start in cell K6.

Step 2) Find Range for Calculation

Move up to K3 and K4 which contains a date/time in each cell. Find all values in between and inclusive of those values in Column A, and the next 30 values in Column A after the K4 time will also be used.
The columns that will be used in the calculations will either be Colum C if it’s a “BUY” or Column D if it’s a “SELL”. For example, if A726:A844 is the range found, cells C726:C844 would be used if K6 showed “BUY”, or cells D726:D844 would be used if K6 showed “SELL”.

Step 3) Calculation with Formula’s

If K6 shows “BUY”

From Column C first cell in the found range, move over to Column K and this is where the data will be input.
The calculation will always be:
=(First cell in Column C of the found range – the Opening Price in correspondent column) * Factor in Cell J9. Copy this formula down until last value found in the range is calculated. The last 30 calculated values in K need to be Bolded/Italicized, or have some type of easily recognizable formatting.

My attached sheet includes the formula, cell referencing, and formatting for clarification.

Then after all the values in K are calculated move up to K6, move to the right 2 columns so we’re in M6 and check if it’s a “BUY” or “SELL”.

If K6 shows “SELL”

From Column D first cell in the found range, move over to Column K and this is where the data will be input.
The calculation will always be:
=(-First Cell in Column D of the found range + Opening Price in correspondent column) * Factor in Cell J9. Copy this formula down until last value found in the range is calculated. The last 30 calculated values in K need to be Bolded/Italicized, or have some type of easily recognizable formatting.
My attached sheet includes the formula, cell referencing, and formatting for clarification located in M826 as K6 did not show a “SELL”.

Then if K6 did show a “SELL” and all values have been calculated, move up to K6, move to the right 2 columns so we’re in M6 and check if it’s a “BUY” or “SELL”.

Repeat this process until there are no more “BUY” or “SELL” in row 6.


I hope the above was clear, and please if you have any question feel free to let me know and I will clarify. Again, I'd be very grateful if anyone can help me out with this macro. Thank you very much (and apologies if this was lengthy as I was trying to be detailed with the request).

Great job so far! We're close.

The “Macro 2” logic needs a slight shift and this is the one where we move 2 columns from K6, to M6, to O6 until end of row checking for "BUY" or "SELL".

For "BUY" it should be: =(First cell in Column C of the found range – the Opening Price in correspondent column) * Factor in Cell J9.
Right now it’s: =(First Cell in Column D of the found range – Opening Price in correspondent column) * Factor in Cell J9.

For "SELL" it should be: =(-First Cell in Column D of the found range + Opening Price in correspondent column) * Factor in Cell J9.
Right now it’s: =(-First Cell in Column C of the found range + Opening Price in correspondent column) * Factor in Cell J9.


But you've nailed the “Macro 1” logic for “BUY” “SELL” where we move 2 columns from L6, to N6, to P6.

Correct formula's whole way through....
For “BUY” it’s: =(First cell in Column D of the found range – the Opening Price in correspondent column) * Factor in Cell J9.
For “SELL” it’s: =(-First Cell in Column C of the found range + Opening Price in correspondent column) * Factor in Cell J9.

Thanks so much, and don't hesitate if you have anymore questions.

sorry for late reply - I was away yesterday. have a go of this...

ASSUMPTIONS: This assumes that your 'macro1' columns will always be even numbered and your date range starts at A10 and cells K9 and L9 are free to be typed in (to store a variable) and then have the contents deleted.

Sub Macro1()
Application.ScreenUpdating = False
'Run across columns getting variables
'Find last column
LastColumn = Range("J6").End(xlToRight).Column

For z = 10 To LastColumn

isOdd = (z Mod 2) - 1 'this stores if the current column is an even or odd number (as the formulas are different for odd/even columns)

'Get variables
BuySell = Cells(6, z).Value
DateTimeStart = Cells(3, z).Value
DateTimeEnd = Cells(4, z).Value + TimeValue("00:30:00")

'Find last used row in column A (So we don't loop all 65000 rows)
Lastrow = Range("A65536").End(xlUp).Row

'Work out the count of cells that fall between this date range (so we can start formatting the last 30)
'print the DateTime variables on the sheet because for some reason the Evaluate formula below wont work with variables
Range("K9") = DateTimeStart
Range("L9") = DateTimeEnd
DatesInThisRange = Evaluate("COUNTIF(A10:A65536,""<=""&L9)-COUNTIF(A10:A65536,""<""&K9)")
'delete those values
Range("K9").ClearContents
Range("L9").ClearContents

'begin a counter so we can check when we get to last 30..
counter = DatesInThisRange

'Run down column A to check if within time range
For x = 10 To Lastrow

'Check if date within range
If Cells(x, 1).Value >= DateTimeStart And Cells(x, 1).Value <= DateTimeEnd Then
'cell is within range! so put formula in...
If isOdd = 0 Then '0 means odd

If BuySell = "Buy" Then
Cells(x, z).FormulaR1C1 = "=(+RC3-R7C)*R9C10"
Else
Cells(x, z).FormulaR1C1 = "=(-RC4+R7C)*R9C10"
End If
Else
If BuySell = "Buy" Then
Cells(x, z).FormulaR1C1 = "=(+RC4-R7C)*R9C10"
Else
Cells(x, z).FormulaR1C1 = "=(-RC3+R7C)*R9C10"
End If
End If
If counter <= 30 Then
Cells(x, z).Font.Bold = True
Cells(x, z).Font.Bold = True
End If
counter = counter - 1
End If
Next
Next
Application.ScreenUpdating = True

End Sub