Re: If Statement Problem

[Robert Hargreaves]

I have changed my code to the same as at the bottom of the page but have 2
issue with the code. I cant specify * as a year for all date find criteria
as the code doesn't allow for this. How could I change the code to allow
for all the 2005's to be replaced with a wildcard.

I also need to add a month to the end of the sheet but depending on which
month it is that is removed there will be a different range. I have written
some code to find the first empty cell which works but arent sure how to
modify a line to use activecell as the variable for adding a new month.

Heres the code

The code for delete is like this.

If ActiveSheet.Range("$A$4").Value = #1/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2008# Then
ActiveSheet.Rows("4:32").Delete
ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2012# Then
ActiveSheet.Rows("4:32").Delete
ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2016# Then
ActiveSheet.Rows("4:32").Delete
ElseIf ActiveSheet.Range("$A$4") = #2/1/2005# Then
ActiveSheet.Rows("4:31").Delete
ElseIf ActiveSheet.Range("$A$4") = #3/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #4/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #5/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #6/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #7/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #8/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #9/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #10/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #11/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #12/1/2005# Then
ActiveSheet.Rows("4:34").Delete
End If

The code for add would be something like this -

Range("A1").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

'This is where I am stuck....

'Range("A5:A6").AutoFill Destination:=Range("A5:A9") - Recommended code to
autofill

'Range(ActiveCell:Activecell - 1).Autofill
Destination:=Range("ActiveCell:????) - code modified to use autofil as
variable which doesnt work.

End Sub

Thanks
Rob

[JE McGimpsey]

One way:

For the delete code (note that for DateSerial the "zeroth" day of the
month is the last day of the previous month):

Dim dtTemp As Double
dtTemp = ActiveSheet.Range("A4").Value
ActiveSheet.Rows(4).Resize(Day(DateSerial( _
Year(dtTemp), Month(dtTemp) + 1, 0))).Delete



In article <[email protected]>,
"Robert Hargreaves" <[email protected]> wrote:

> I have changed my code to the same as at the bottom of the page but have 2
> issue with the code. I cant specify * as a year for all date find criteria
> as the code doesn't allow for this. How could I change the code to allow
> for all the 2005's to be replaced with a wildcard.
>
> I also need to add a month to the end of the sheet but depending on which
> month it is that is removed there will be a different range. I have written
> some code to find the first empty cell which works but arent sure how to
> modify a line to use activecell as the variable for adding a new month.
>
> Heres the code
>
> The code for delete is like this.
>
> If ActiveSheet.Range("$A$4").Value = #1/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2008# Then
> ActiveSheet.Rows("4:32").Delete
> ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2012# Then
> ActiveSheet.Rows("4:32").Delete
> ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2016# Then
> ActiveSheet.Rows("4:32").Delete
> ElseIf ActiveSheet.Range("$A$4") = #2/1/2005# Then
> ActiveSheet.Rows("4:31").Delete
> ElseIf ActiveSheet.Range("$A$4") = #3/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #4/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #5/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #6/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #7/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #8/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #9/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #10/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #11/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #12/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> End If
>
> The code for add would be something like this -
>
> Range("A1").Select
>
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
>
> Loop Until IsEmpty(ActiveCell) = True
>
> 'This is where I am stuck....
>
> 'Range("A5:A6").AutoFill Destination:=Range("A5:A9") - Recommended code to
> autofill
>
> 'Range(ActiveCell:Activecell - 1).Autofill
> Destination:=Range("ActiveCell:????) - code modified to use autofil as
> variable which doesnt work.
>
> End Sub
>
> Thanks
> Rob
>
>

[JMB]

if you want to replace 2005 with an *, you could use the LIKE operator istead
of the "=" operator. LIKE has a number of options for to use in pattern
matching. VBA help file can give you more details.


"Robert Hargreaves" wrote:

> I have changed my code to the same as at the bottom of the page but have 2
> issue with the code. I cant specify * as a year for all date find criteria
> as the code doesn't allow for this. How could I change the code to allow
> for all the 2005's to be replaced with a wildcard.
>
> I also need to add a month to the end of the sheet but depending on which
> month it is that is removed there will be a different range. I have written
> some code to find the first empty cell which works but arent sure how to
> modify a line to use activecell as the variable for adding a new month.
>
> Heres the code
>
> The code for delete is like this.
>
> If ActiveSheet.Range("$A$4").Value = #1/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2008# Then
> ActiveSheet.Rows("4:32").Delete
> ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2012# Then
> ActiveSheet.Rows("4:32").Delete
> ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2016# Then
> ActiveSheet.Rows("4:32").Delete
> ElseIf ActiveSheet.Range("$A$4") = #2/1/2005# Then
> ActiveSheet.Rows("4:31").Delete
> ElseIf ActiveSheet.Range("$A$4") = #3/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #4/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #5/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #6/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #7/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #8/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #9/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #10/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> ElseIf ActiveSheet.Range("$A$4") = #11/1/2005# Then
> ActiveSheet.Rows("4:33").Delete
> ElseIf ActiveSheet.Range("$A$4") = #12/1/2005# Then
> ActiveSheet.Rows("4:34").Delete
> End If
>
> The code for add would be something like this -
>
> Range("A1").Select
>
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
>
> Loop Until IsEmpty(ActiveCell) = True
>
> 'This is where I am stuck....
>
> 'Range("A5:A6").AutoFill Destination:=Range("A5:A9") - Recommended code to
> autofill
>
> 'Range(ActiveCell:Activecell - 1).Autofill
> Destination:=Range("ActiveCell:????) - code modified to use autofil as
> variable which doesnt work.
>
> End Sub
>
> Thanks
> Rob
>
>
>
>
>
>