VBA date check, scan range, modify cell...

[Snolune]

I'm not sure if this is even possible.
I am wanting to check the current system date and than do the following.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]PP[/TD]
[TD]From[/TD]
[TD][/TD]
[TD]To[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]7 Jan[/TD]
[TD]-[/TD]
[TD]20 Jan[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]21 Jan[/TD]
[TD]-[/TD]
[TD]3 Feb[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5[/TD]
[TD]4 Feb[/TD]
[TD]-[/TD]
[TD]17 Mar[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]6[/TD]
[TD]18 Feb[/TD]
[TD]-[/TD]
[TD]31 Mar[/TD]
[/TR]
</tbody>[/TABLE]

Scan columns B and D to find where the current date would fall.
When found take the number found in column A on the row where the date was found and apply it to cell C7.

 

[SteveO59L]

so, today being 19 Jan, would fall between 7 Jan - 20 Jan, and cell c10 would be populated with 3?

 

[Snolune]

Correct on the first part, but after finding the date it would modify cell c7 with the number located in column A and the row where the date was found. So for the example you provided it would be row 10.

 

[SteveO59L]

something along the lines of

Dim mydate As Date
Dim c As Range, rng
mydate = Date
Set rng = Range("b10:b13")
For Each c In rng
If mydate >= c.Value And mydate <= c.Offset(0, 2).Value Then

Range("c7") = c.Offset(0, -1)
End If
Next c