VBA - Finding and copy row with first value >&< certain threshold

[tmvds]

Hi everyone, I am new to VBA and would really appreciate some advice with this task.

I have a database with a changing number of entries per day (column A - some days 5 entries, others 20 etc).
I need to find and copy into a new sheet the entire row, for each day, for the first entry that has a value in column B which is lower & greater than a threshold (let's say 70%).

To be clear, let's suppose 28th March has 5 entries and 29th March 3 entries:

1 28/03 90% A
2 28/03 73% B
3 28/03 68% C
4 28/03 65% D
5 28/03 50% E
6 29/03 74% F
7 29/03 50% G
8 29/03 65% H

I need the VBA code to find the first value above (73%) and below (68%), and copy these entire 2 rows in a new sheet. Then move on to 29/03, and copy rows 6 & 8 into the new sheet. The final result in the new sheet should be:


2 28/03 73% B
3 28/03 68% C
6 29/03 74% F
8 29/03 65% H


Any suggestion?
Thanks a lot for your help.

[tmvds]

Just to specify, it can never happen that the same day has the same 2 entries.
So it is not possible to have:

1 28/03 68% A
2 28/03 73% B
3 28/03 68% C


To be clear, column D contains numbers too and I need, for each day, an interpolated value at 70%. Since there won't be an exact observation at 70%, I need to get the first value above and the first value below 70% and linearly interpolate.

for example:

1 28/03 90% 100
2 28/03 75% 80
3 28/03 65% 70
4 28/03 60% 40
5 28/03 50% 30

I will need to copy row 2 & 3 to a new sheet and then interpolate a value for 70%, which in this case will be 75. Possibly copy this entry into a new sheet too, so to have for each day the interpolated value at 70%.

NewSheet1 ===>

2 28/03 75% 80
3 28/03 65% 70

NewSheet2 ===>

1 28/03 70% 75



The two other issues I can think of are:
1) the distance of the observations above and below the 70% threshold are not always equal. One day can be 75%-65%, another 73%-69%. So to interpolate the exact 70% I will need to take this into account.

2) It can happen that somedays do not have observation above or below 70%. So if, for example, a day has ONLY obs above, I will need to get the first TWO obs above 70% and linearly get what should be the corresponding value at 70%.

ie:
1 28/03 90% 200
2 28/03 80% 150
3 28/03 75% 100

Delta 75-80%= 5% : 50=150-100 ===> 70% = 50

If needed I will try to be more exhaustive, thanks for your help. Much appreciated