Okay, hopefully this is the last question today!

I've found the below formula in one of the posts here, but my brain has 
completely failed on me and I'm not sure how to adapt it to my requirements.

I have a value which is entered by the user after a prompt, that populates 
in cell G4 in a sheet called 'Lookup'.  For arguments sake, the current value 
is 30/11/09.

  I have another worksheet, 'Data', which has a column populated with dates 
(say column B).  I'd like a macro that looks in column B on the data sheet, 
and clears out any dates which are greater than the value in cell G4.  I just 
want to empty those cells, not delete columns or anything else.  

Sub sth()
Dim cell As Range

For Each cell In Selection
    If cell.Value < 1000 Then
        cell.ClearContents
    End If
Next cell


End Sub

Hi,

Try this

Sub sth()
Dim LastRow as long
lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
For Each c In MyRange
    If c.Value > Sheets("lookup").Range("G4") Then
        c.ClearContents
    End If
Next

Mike

"bawpie" wrote:

> Okay, hopefully this is the last question today!
> 
> I've found the below formula in one of the posts here, but my brain has 
> completely failed on me and I'm not sure how to adapt it to my requirements.
> 
> I have a value which is entered by the user after a prompt, that populates 
> in cell G4 in a sheet called 'Lookup'.  For arguments sake, the current value 
> is 30/11/09.
> 
>   I have another worksheet, 'Data', which has a column populated with dates 
> (say column B).  I'd like a macro that looks in column B on the data sheet, 
> and clears out any dates which are greater than the value in cell G4.  I just 
> want to empty those cells, not delete columns or anything else.  
> 
> Sub sth()
> Dim cell As Range
> 
> For Each cell In Selection
>     If cell.Value < 1000 Then
>         cell.ClearContents
>     End If
> Next cell
> 
> 
> End Sub
> 

Hi,

If you want to do it with 'selection' then select the range and use this

Sub sth()
For Each c In Selection
    If c.Value > Sheets("lookup").Range("G4") Then
        c.ClearContents
    End If
Next
End Sub

Mike

"Mike H" wrote:

> Hi,
> 
> Try this
> 
> Sub sth()
> Dim LastRow as long
> lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
> Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
> For Each c In MyRange
>     If c.Value > Sheets("lookup").Range("G4") Then
>         c.ClearContents
>     End If
> Next
> 
> Mike
> 
> "bawpie" wrote:
> 
> > Okay, hopefully this is the last question today!
> > 
> > I've found the below formula in one of the posts here, but my brain has 
> > completely failed on me and I'm not sure how to adapt it to my requirements.
> > 
> > I have a value which is entered by the user after a prompt, that populates 
> > in cell G4 in a sheet called 'Lookup'.  For arguments sake, the current value 
> > is 30/11/09.
> > 
> >   I have another worksheet, 'Data', which has a column populated with dates 
> > (say column B).  I'd like a macro that looks in column B on the data sheet, 
> > and clears out any dates which are greater than the value in cell G4.  I just 
> > want to empty those cells, not delete columns or anything else.  
> > 
> > Sub sth()
> > Dim cell As Range
> > 
> > For Each cell In Selection
> >     If cell.Value < 1000 Then
> >         cell.ClearContents
> >     End If
> > Next cell
> > 
> > 
> > End Sub
> > 

Okay,

I've tried the below, and it is working except:

For some reason it deletes the header from B1 (this is easily solved though 
by setting the range as B2:B & Last Row).

Also, I'm using the following piece of code for the input (G4)

Sheets("Lookup").Range("G4").Value = InputBox("Please enter the end of 
Period (dd/mm/yy)", "Report Year")

Now I think when it's populating the box, it's doing so as a string rather 
than a number - so the macro doesn't work (although funnily enough that 
reference does work as a date in a formula I'm also using).  If I just type 
in a date (say into G5) and then reference that, the macro understands it 
perfectly.

So I guess I need to figure out how to tweak my input so it's returned as a 
true date rather than a string? I tried just recording macro which would 
simply change G4 to text to columns prior to running your code (messy yes, 
but I thought it would work) but it doesn't seem to...

"Mike H" wrote:

> Hi,
> 
> Try this
> 
> Sub sth()
> Dim LastRow as long
> lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
> Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
> For Each c In MyRange
>     If c.Value > Sheets("lookup").Range("G4") Then
>         c.ClearContents
>     End If
> Next
> 
> Mike
> 
> "bawpie" wrote:
> 
> > Okay, hopefully this is the last question today!
> > 
> > I've found the below formula in one of the posts here, but my brain has 
> > completely failed on me and I'm not sure how to adapt it to my requirements.
> > 
> > I have a value which is entered by the user after a prompt, that populates 
> > in cell G4 in a sheet called 'Lookup'.  For arguments sake, the current value 
> > is 30/11/09.
> > 
> >   I have another worksheet, 'Data', which has a column populated with dates 
> > (say column B).  I'd like a macro that looks in column B on the data sheet, 
> > and clears out any dates which are greater than the value in cell G4.  I just 
> > want to empty those cells, not delete columns or anything else.  
> > 
> > Sub sth()
> > Dim cell As Range
> > 
> > For Each cell In Selection
> >     If cell.Value < 1000 Then
> >         cell.ClearContents
> >     End If
> > Next cell
> > 
> > 
> > End Sub
> > 

Like this

Sheets("Lookup").Range("G4").Value = _
Format(InputBox("Please enter the end of Period (dd/mm/yy)", "Report Year"), 
"dd/mm/yyyy")


Mike

"bawpie" wrote:

> Okay,
> 
> I've tried the below, and it is working except:
> 
> For some reason it deletes the header from B1 (this is easily solved though 
> by setting the range as B2:B & Last Row).
> 
> Also, I'm using the following piece of code for the input (G4)
> 
> Sheets("Lookup").Range("G4").Value = InputBox("Please enter the end of 
> Period (dd/mm/yy)", "Report Year")
> 
> Now I think when it's populating the box, it's doing so as a string rather 
> than a number - so the macro doesn't work (although funnily enough that 
> reference does work as a date in a formula I'm also using).  If I just type 
> in a date (say into G5) and then reference that, the macro understands it 
> perfectly.
> 
> So I guess I need to figure out how to tweak my input so it's returned as a 
> true date rather than a string? I tried just recording macro which would 
> simply change G4 to text to columns prior to running your code (messy yes, 
> but I thought it would work) but it doesn't seem to...
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > Try this
> > 
> > Sub sth()
> > Dim LastRow as long
> > lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
> > Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
> > For Each c In MyRange
> >     If c.Value > Sheets("lookup").Range("G4") Then
> >         c.ClearContents
> >     End If
> > Next
> > 
> > Mike
> > 
> > "bawpie" wrote:
> > 
> > > Okay, hopefully this is the last question today!
> > > 
> > > I've found the below formula in one of the posts here, but my brain has 
> > > completely failed on me and I'm not sure how to adapt it to my requirements.
> > > 
> > > I have a value which is entered by the user after a prompt, that populates 
> > > in cell G4 in a sheet called 'Lookup'.  For arguments sake, the current value 
> > > is 30/11/09.
> > > 
> > >   I have another worksheet, 'Data', which has a column populated with dates 
> > > (say column B).  I'd like a macro that looks in column B on the data sheet, 
> > > and clears out any dates which are greater than the value in cell G4.  I just 
> > > want to empty those cells, not delete columns or anything else.  
> > > 
> > > Sub sth()
> > > Dim cell As Range
> > > 
> > > For Each cell In Selection
> > >     If cell.Value < 1000 Then
> > >         cell.ClearContents
> > >     End If
> > > Next cell
> > > 
> > > 
> > > End Sub
> > > 

Mike,

Thanks.  I've tried that, and when G4 is populated it is formatted as 
30/11/2009 but it's still a string rather than a number, so the previous 
macro doesn't work.

If I enter a date in the cell below, it enters as a date and goes to the 
left side of the cell - but the data taken from the input box sits on the 
right (which is behaviour for text I think?)  Checking the format type on 
both cells reveals them to be date formatted.  

I know I can simply have the user put the date into a box, but I liked the 
idea of having the input box as it would force them to update prior to 
running the macro.

Thanks for your continued assistance!

"Mike H" wrote:

> Like this
> 
> Sheets("Lookup").Range("G4").Value = _
> Format(InputBox("Please enter the end of Period (dd/mm/yy)", "Report Year"), 
> "dd/mm/yyyy")
> 
> 
> Mike
> 
> "bawpie" wrote:
> 
> > Okay,
> > 
> > I've tried the below, and it is working except:
> > 
> > For some reason it deletes the header from B1 (this is easily solved though 
> > by setting the range as B2:B & Last Row).
> > 
> > Also, I'm using the following piece of code for the input (G4)
> > 
> > Sheets("Lookup").Range("G4").Value = InputBox("Please enter the end of 
> > Period (dd/mm/yy)", "Report Year")
> > 
> > Now I think when it's populating the box, it's doing so as a string rather 
> > than a number - so the macro doesn't work (although funnily enough that 
> > reference does work as a date in a formula I'm also using).  If I just type 
> > in a date (say into G5) and then reference that, the macro understands it 
> > perfectly.
> > 
> > So I guess I need to figure out how to tweak my input so it's returned as a 
> > true date rather than a string? I tried just recording macro which would 
> > simply change G4 to text to columns prior to running your code (messy yes, 
> > but I thought it would work) but it doesn't seem to...
> > 
> > "Mike H" wrote:
> > 
> > > Hi,
> > > 
> > > Try this
> > > 
> > > Sub sth()
> > > Dim LastRow as long
> > > lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
> > > Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
> > > For Each c In MyRange
> > >     If c.Value > Sheets("lookup").Range("G4") Then
> > >         c.ClearContents
> > >     End If
> > > Next
> > > 
> > > Mike
> > > 
> > > "bawpie" wrote:
> > > 
> > > > Okay, hopefully this is the last question today!
> > > > 
> > > > I've found the below formula in one of the posts here, but my brain has 
> > > > completely failed on me and I'm not sure how to adapt it to my requirements.
> > > > 
> > > > I have a value which is entered by the user after a prompt, that populates 
> > > > in cell G4 in a sheet called 'Lookup'.  For arguments sake, the current value 
> > > > is 30/11/09.
> > > > 
> > > >   I have another worksheet, 'Data', which has a column populated with dates 
> > > > (say column B).  I'd like a macro that looks in column B on the data sheet, 
> > > > and clears out any dates which are greater than the value in cell G4.  I just 
> > > > want to empty those cells, not delete columns or anything else.  
> > > > 
> > > > Sub sth()
> > > > Dim cell As Range
> > > > 
> > > > For Each cell In Selection
> > > >     If cell.Value < 1000 Then
> > > >         cell.ClearContents
> > > >     End If
> > > > Next cell
> > > > 
> > > > 
> > > > End Sub
> > > > 

Ah, got it.  Changed my input to:

Dim Period As Date
    
    Period = InputBox("Please enter the end of Period (dd/mm/yyyy)", "Report 
Year")
    
    Sheets("Lookup").Select
    Range("G4").Value = Period

And now it works flawlessly with your macro.  Thanks very much!


"bawpie" wrote:

> Mike,
> 
> Thanks.  I've tried that, and when G4 is populated it is formatted as 
> 30/11/2009 but it's still a string rather than a number, so the previous 
> macro doesn't work.
> 
> If I enter a date in the cell below, it enters as a date and goes to the 
> left side of the cell - but the data taken from the input box sits on the 
> right (which is behaviour for text I think?)  Checking the format type on 
> both cells reveals them to be date formatted.  
> 
> I know I can simply have the user put the date into a box, but I liked the 
> idea of having the input box as it would force them to update prior to 
> running the macro.
> 
> Thanks for your continued assistance!
> 
> "Mike H" wrote:
> 
> > Like this
> > 
> > Sheets("Lookup").Range("G4").Value = _
> > Format(InputBox("Please enter the end of Period (dd/mm/yy)", "Report Year"), 
> > "dd/mm/yyyy")
> > 
> > 
> > Mike
> > 
> > "bawpie" wrote:
> > 
> > > Okay,
> > > 
> > > I've tried the below, and it is working except:
> > > 
> > > For some reason it deletes the header from B1 (this is easily solved though 
> > > by setting the range as B2:B & Last Row).
> > > 
> > > Also, I'm using the following piece of code for the input (G4)
> > > 
> > > Sheets("Lookup").Range("G4").Value = InputBox("Please enter the end of 
> > > Period (dd/mm/yy)", "Report Year")
> > > 
> > > Now I think when it's populating the box, it's doing so as a string rather 
> > > than a number - so the macro doesn't work (although funnily enough that 
> > > reference does work as a date in a formula I'm also using).  If I just type 
> > > in a date (say into G5) and then reference that, the macro understands it 
> > > perfectly.
> > > 
> > > So I guess I need to figure out how to tweak my input so it's returned as a 
> > > true date rather than a string? I tried just recording macro which would 
> > > simply change G4 to text to columns prior to running your code (messy yes, 
> > > but I thought it would work) but it doesn't seem to...
> > > 
> > > "Mike H" wrote:
> > > 
> > > > Hi,
> > > > 
> > > > Try this
> > > > 
> > > > Sub sth()
> > > > Dim LastRow as long
> > > > lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
> > > > Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
> > > > For Each c In MyRange
> > > >     If c.Value > Sheets("lookup").Range("G4") Then
> > > >         c.ClearContents
> > > >     End If
> > > > Next
> > > > 
> > > > Mike
> > > > 
> > > > "bawpie" wrote:
> > > > 
> > > > > Okay, hopefully this is the last question today!
> > > > > 
> > > > > I've found the below formula in one of the posts here, but my brain has 
> > > > > completely failed on me and I'm not sure how to adapt it to my requirements.
> > > > > 
> > > > > I have a value which is entered by the user after a prompt, that populates 
> > > > > in cell G4 in a sheet called 'Lookup'.  For arguments sake, the current value 
> > > > > is 30/11/09.
> > > > > 
> > > > >   I have another worksheet, 'Data', which has a column populated with dates 
> > > > > (say column B).  I'd like a macro that looks in column B on the data sheet, 
> > > > > and clears out any dates which are greater than the value in cell G4.  I just 
> > > > > want to empty those cells, not delete columns or anything else.  
> > > > > 
> > > > > Sub sth()
> > > > > Dim cell As Range
> > > > > 
> > > > > For Each cell In Selection
> > > > >     If cell.Value < 1000 Then
> > > > >         cell.ClearContents
> > > > >     End If
> > > > > Next cell
> > > > > 
> > > > > 
> > > > > End Sub
> > > > >