Be seen. Boost your question’s priority for more expert views and faster solutions
Step 1 By clicking button process start in Sheet “Data”I'm really not sure what I'm supposed to know about this step. What does this have to do with anything? Is this just a description of where you want a button to fire something off? Other than that I'm not sure what you're wanting done.
Step 2 if there is “High” in Cell L3 then have to find minimum value from column D from range - date in Cell N3 (which is in our case it is 1/4/2011 & it is in data table column A header ”Date” in row 4) to next Hit found.Why would there be a "High' in cell L3? Do you list them there? When? How? Why? What if it isn't "High"? If I'm understanding this correctly, you will enter values in column L and N, either High or Low, and a date, and you want the corresponding high/low value returned from that date. If that is the case, the formula for that is easy and becomes...
=VLOOKUP(N3,$A:$F,LOOKUP(L3,{"Close","High","Low"},{5,3,4}),0)
How do I get “Hit” is calculated in attached workbook –sheet – A. See Column H & I i.e. Gap & Hit. Calculation starts from one row lower from actual one.as we find Hit, have to find minimum value From range hit row number to start date row number from column D i.e. Header “Low”. Calculation formula for High is mentioned in cell H5 & I5 Respectively.
You stop at where there is no data in column A. Or say range A:E. So last formula drop down (step 5) till last data available in range A:E.But I'm still uncertain as to what that actually means. It doesn't make sense.
You stop at where there is no data in column A.What do you mean? What is stopped? What data?
Or say range A:E. So last formula drop down (step 5) till last data available in range A:E.I'm still lost with this. What formula, and what drop down? And what is the last data available in range? The last row of the table? What is the cell address you're talking about?
IF Cell L3 = High then we are finding Lowest Low. What I did is From High i.e. M3 minus Lows of each row (Column D). & added one criteria if above value is greater than current result then above value else current value. i.e. =IF($M$3-D5>H4,$M$3-D5,H4)
& For HIT
there is 2 Criteria to match- Criteria 1 Gap i.e. Current example Cell H5 is greater than or equal to forward 5 rows Gap i.e. Cell H10. Criteria 2 61.80% of Gap value – High point (Cell M3) is lesser High (Cell C5) then Hit else “”.
Both are matched then "Hit" else "" i.e. =IF(AND(H5>=H10,$M$3-(H5*61.8%)<C5), "Hit","").
Now For Low if Cell L3=Low then finding highest high from range between low to next hit. Formula flow is like this for gap - current high (column C) - Low (Cell M3). Here also I had add criteria previous value is greater than this value then previous value else this value. i.e. =IF(C5-$M$3>H4,C5-$M$3,H4).
For Hit two - criteria 1 forward 5 row value is greater than or equal to. Criteria 2 61.80% of Gap plus Lowest point is greater than Low (column D). i.e. =IF(AND(H5>=H10,$M$3+(H5*61.8%)>D5), "Hit","")
And I had combine this formula if high then this if low then this else ""
For both Gap & Hit column
=IF($L$3="High",IF($M$3-D5>H4,$M$3-D 5,H4),IF($ L$3="Low", IF(C5-$M$3 >H4,C5-$M$ 3,H4),""))
=IF($L$3="High",IF(AND(H5>=H10,$M$3- (H5*61.8%) <C5),"Hit" ,""),IF($L $3="Low",I F(AND(H5>= H10,$M$3+( H5*61.8%)> D5),"Hit", ""),""))
Are you saying that the formulas need to cleared and entered againYes
from the row where the "hit" was last found?No
Sub Calculate(Optional ByVal iDateRow As Integer = 3)
Dim curRow, curHit As Integer
Dim newVal As Single
Dim newRow As Integer
' find the date from N3 in Col A
curRow = FindDate(iDateRow)
' If we find a date
If (curRow <> 0) Then
'put the formulas here and fill down
Range("A3").Select
Selection.End(xlDown).Select
Dim lastRow As Integer
lastRow = ActiveCell.Row
WriteFormulas curRow, lastRow
Application.Calculate
Else
' Exit if date could not be found
Exit Sub
End If
curHit = FindHit(curRow)
If (curHit <> 0) Then
Select Case Range("L3").Value
Case "High"
newVal = Evaluate("=MIN(D" & curRow & ":D" & curHit & ")")
newRow = FindMaxMinRow(curRow, curHit, "D", False)
newVal = Range("D" & newRow).Value
CopyDataDown
Range("L3").Value = "Low"
Range("M3").Value = newVal
Range("N3").Value = Range("A" & newRow).Value
Case "Low"
newVal = Evaluate("=MAX(C" & curRow & ":C" & curHit & ")")
newRow = FindMaxMinRow(curRow, curHit, "C", True)
newVal = Range("C" & newRow).Value
CopyDataDown
Range("L3").Value = "High"
Range("M3").Value = newVal
Range("N3").Value = Range("A" & newRow).Value
End Select
' If (MsgBox("Ok for Next or Cancel", vbOKCancel, "Step by Step") = vbCancel) Then
' Exit Sub
' End If
'Range(Cells(3, 8), Cells(curRow, 8)).Clear
Cells(curRow, 1).Select
Application.Calculate
Calculate curRow
Else
'could not find a hit so finish
MsgBox "Finished"
Range("L3").Select
End If
End Sub
Maths-2.xlsm
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Can you elaborate a bit more on your instructions? Like, a lot more? It's difficult at best to follow what you want.
What does this mean? What are the different formulas? Where are they at? Does this mean anything for us?
I think I can follow this. Still a little shaky on the details though. For example, are the dates in N3 and N4 a date range? Do you only want to find the next highest value after the date in N3? You could achieve this a couple of ways, either array-entered formula, which is confirmed with CTRL + SHIFT + ENTER, instead of just ENTER, but we'd need to know specifics. You could do this with a non-array entered formula as well, but again, more specifics are needed.
We need clear and concise explanations to give you any sort of help. Please list them out in a post and not in an attached file. In a file attachment we'd really only like to see two things, 1) the data as you have it to start, and 2) the desired outcome. All directions and instructions should be in a post.
Regards,
Zack Barresse