You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Excel Help
- Thread starter JDConner
- Start date
omniscient
Guest
i'm not sure i follow. is this your scenario?
stock A (H/L): 25.00/24.00
stock B (H/L): 25.10/24.20
so the spread between these two stocks is:
25.10 (max of the stocks) - 24.00 (min of the stocks)
stock A (H/L): 25.00/24.00
stock B (H/L): 25.10/24.20
so the spread between these two stocks is:
25.10 (max of the stocks) - 24.00 (min of the stocks)
This should work
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If IsNumeric(Sheets("sheet1").Range("A1")) Then
If Sheets("sheet1").Range("A1") > Sheets("sheet1").Range("B1") Then
Sheets("sheet1").Range("B1") = Sheets("sheet1").Range("A1")
End If
If Sheets("sheet1").Range("A1") < Sheets("sheet1").Range("C1") Then
Sheets("sheet1").Range("C1") = Sheets("sheet1").Range("A1")
End If
End If
End Sub
Where Sheet1 is the name of whatever sheet the cells in question are on, A1 has the DDE value, and B1 is the cell you want to copy the Max value to, and C1 is the min. Remember, blank cells are treated as 0, that might be important for the Min side of this.
To plug this in, open your excel workbook, hit Alt-F11 and double click on "this workbook" in the list on the left (much easier if only one excel file is open at the time, because each workbook will have "this workbook") and paste the code in there.
Save the work book and everytime it recalculates any value from then on it will check the Min/Max and copy them over if it is higher/lower than the value in B1/C1.
Hope that all makes sense....
- The New Guy
PS not too sure about good books, but do some google searches on "Excel VBA" and you should get some good resources.
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If IsNumeric(Sheets("sheet1").Range("A1")) Then
If Sheets("sheet1").Range("A1") > Sheets("sheet1").Range("B1") Then
Sheets("sheet1").Range("B1") = Sheets("sheet1").Range("A1")
End If
If Sheets("sheet1").Range("A1") < Sheets("sheet1").Range("C1") Then
Sheets("sheet1").Range("C1") = Sheets("sheet1").Range("A1")
End If
End If
End Sub
Where Sheet1 is the name of whatever sheet the cells in question are on, A1 has the DDE value, and B1 is the cell you want to copy the Max value to, and C1 is the min. Remember, blank cells are treated as 0, that might be important for the Min side of this.
To plug this in, open your excel workbook, hit Alt-F11 and double click on "this workbook" in the list on the left (much easier if only one excel file is open at the time, because each workbook will have "this workbook") and paste the code in there.
Save the work book and everytime it recalculates any value from then on it will check the Min/Max and copy them over if it is higher/lower than the value in B1/C1.
Hope that all makes sense....
- The New Guy
PS not too sure about good books, but do some google searches on "Excel VBA" and you should get some good resources.