PDA

View Full Version : Compare Dates and Clear Cell Contents



hobbiton73
01-22-2013, 11:33 AM
Hi, I wonder whether someone may be able to help me please.

I'm using the attached file to allow users to record their work forecasts.

What I'm trying to do, and having great difficulty in doing so, is to compare two sets of date values, then clearing the relevant cell contents.

In the file you'll be able to see the following:
'Monthly' column headings in row 6. These have been given the named range "ForecastsMonth".
Date value in column "AB", and
Forecast values in ranges "A7:A21" and "N7:Y21"What I'd like to do is initially check to see if there is a date value in column "AB", if there is, then compare this value against the month values in the named range.

If there any monthly values in the named range greater than the date in column AB, I would like to delete the relevant work forecast on that row.

For example, in my test file, you'll see that there is a value of 01/01/13 (formatted as Jan 13) in column "AB".

If this is compared against the month column headings in the named range, it's clear that months Feb 13 to March 14 are greater than this value.

This means that I'd like to clear the contents of K7:L7 and N7:R7

I've put together the following:

With Target
Select Case True
Case .Column = 28

If .Value2 <> "" Then
If .Value2 < Range("ForecastsMonth") Then
Range("A" & Target.Row & ":L" & Target.Row).ClearContents = True
Range("N" & Target.Row & ":Y" & Target.Row).ClearContents = True
End If
End If
Case Else
End Select
End With


But, although I don't receive an error message, it doesn't delete the values.

I've been working on this all day and tried more variations than I care to mention.

I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.

Many thanks and kind regards

Chris

werafa
01-22-2013, 10:02 PM
I suspect you will need to:
for each myCell in myRange
and test each number individually. If I understand it, you are testing your primary value against 14 possible options.

Tim

hobbiton73
01-23-2013, 09:38 AM
Hi @werafa, thank you for taking the time to reply to my post and for the guidance. I'll take a look at this.

Many thanks and kind regards

Chris