|
If Statement Problem
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 |
If Statement Problem
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 , "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 |
If Statement Problem
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 |
| All times are GMT +1. The time now is 10:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com