Hi, I have a question regarding if-statements in VBA. I have a time-series of interest rates (A2) and calculated the daily percentage changes (B2). Now I need to find out how long it takes until the change since the first day (B2) is grows bigger than 2%. Therefore I need to test for B2:B3 if change >2%. If it is not then it has to calculate if the change from B2:B4 is >2%. If not, then it has to calculate if B2:B5>2% and so forth. As soon as the percentage change is bigger then 2% since start, I need VBA to give me the number of days that have passed since the first day (B2) until the >2% change. Would be very helpful if you could give me a hint or the code how to programme this in VBA. Many thanks! Timi

Your "expressions" are a little confusing. The syntax B2:B4 means the range B2 through B4, i.e. B2, B3 and B4. Similarly, B2:B5 means B2, B3, B4, B5. Therefore "it has to calculate if B2:B5>2%" doesn't make sense in the context of the rest of your post, at least not to me. Are you trying to determine how many days it takes until the difference between B5 and B2 is > 2%, e.g. "If B5 - B2 > 2% Then..."? If so, this code might do it for you. Change the upper limit of the loop (10) to fit your needs. Sub HowLong2Percent() For myDays = 3 To 10 If Range("B" & myDays) - Range("B2") > 0.02 Then MsgBox myDays - 2 Exit Sub End If Next End Sub

Hi, apologies for using the wrong syntaxes. I am not sure if the code does what I need. I try to explain it differently: I have one row with daily changes of interest rates. Now I have to calculate how many days it took until the change grow bigger than 2%. Maybe the change from day t+1 and day t is already bigger than 2%. Otherwise the macro has to check if the change from between days t+2 and t is bigger than 2%, than if the change between days t+3 and t is bigger than 2% and so on. This check has to be done until the change is >2% thank you!

re:I am not sure if the code does what I need. What do you mean by "I'm not sure"? Did you try it? Either it does what you want or it doesn't. If it doesn't, tell us why. re: I have one row with daily changes of interest rates In your OP, you used syntaxes like B2:B3, B2:B4, etc. That tells us your data is in a column, not a row. Which is it? Here is what the code that I offered does: Let's say your value for t is in B2 (based on your OP). That puts t+1 in B3, t+2 in B4, t+x in B(x+2), etc. Sub HowLong2Percent() For myDays = 3 To 10 The first time through the loop, myDays = 3 If Range("B" & myDays) - Range("B2") > 0.02 Then Calculate B3 - B2. In others words, (t+1) - t. If it's > .02 (2%) then... MsgBox myDays - 2 Display myDays (in this case, 3) minus 2 which equals 1. Therefore it took 1 day for the difference between t and (t + x) to be > 2% Exit Sub We're done when (t+x) - t > .02 End If Required to end the IF statement. Next If t+1 (which lives in B3) was not > .02, then increment myDays to 4 and calculate the difference between t+2 and t. In other words, B4 - B2. If this value is > .02, then display myDays - 2 (i.e. 4 -2) which equals 2 and it took 2 days to increase to > .02. Loop until (t+x) - t > .02. I stopped the loop at 10 only because I don't know how many (t+x)'s you have. You can make the loop as long as you need. End Sub I believe this does what you asked for in your OP and also in your latest response, ignoring the fact that in your OP the data was in Column B and in your latest response it's in a row.