Hi Hadamhiram,
Try this:
Function TrailingStopLoss() As String
Dim Counter As Integer 'a counter for the FOR loop iterations
Dim Previous_Max As Double 'max value so far; updated in the FOR loop if conditions are met
Dim Trailing_Stop_Address As Range 'address of cell where trailing stop is found, if one exists
Dim Trailing_Stop_Found As Boolean 'is a trailing stop loss found?
ActiveCell.Range = Application.Caller.Offset(0, -1) 'sets first cell to start from, i.e. the cell to the left of the function
Previous_Max = 0 'sets initial Previous_Max value to 0
Trailing_Stop_Found = False 'sets initial Trailing_Stop_Found to False
For Counter = 1 To 50 'start of the for loop; counts number of rows equal to analytic interval
If ActiveCell.Value > Previous_Max Then 'Tests to see if the current cell's value > Previous_Max
MAX = ActiveCell.Value 'updates the Max variable if the current row has a larger value
ElseIf ActiveCell.Value < (Previous_Max - 3) Then 'tests if the current cell is less than the previous max minus 3
Trailing_Stop_Found = True 'if true, assigns Trailing_Stop_Found = True
Trailing_Stop_Address = ActiveCell.Address 'assigns the current cell address to Traiing_Stop_Address
Exit For 'breaks the FOR loop, since there's no need to do the rest of the iterations
End If
ActiveCell.Offset(1, 0).Select
Next 'continues loop if no trailing stop address is found
If Trailing_Stop_Found = True Then 'if a trailing stop was found, the function returns address of that cell
TrailingStopLoss = Trailing_Stop_Address
ElseIf Trailing_Stop_Found = False Then 'if not found, the function returns "no stop loss found"
TrailingStopLoss = "no stop loss found"
End If
End Function
But it needs to be in a Standard Code Module in a Macro-Enabled Workbook:
Directions for running the routine(s) just supplied
Copy the code to the clipboard
Open your Workbook
Save your Workbook or Another Workbook designated for Macros as Macro_Enabled
Press ALT + F11 to open the Visual Basic Editor.
Select “Module” from the Insert menu
Type "Option Explicit" then paste the code under it
With the cursor between Sub and End Sub press F5 (F8 to Single Step)
OR
Press ALT + Q to close the code window.
Press ALT + F8 then double click on the macro name
Bookmarks