I actually spotted that typo and fixed it, but your explanation made it
easier to understand. However, I now receive a 'Run Time Error 424' Object
Required. I've tried to figure it out myself, but have been unable. Thanks
again for your help, Patrick.
"Patrick Molloy" wrote:
> I see a typo I'm afraid
>
> line
> Set target = target.ffset(1)
> should be
> Set target = target.Offset(1)
>
> strange since its a cut/paste from my own test module. apologies. Use
> debug/compile to find these
>
> we need to get this month and next month from the date you have in cell B1
> so we get the year & month from B1, which is the system date, and set the
> day to 1
> this is saved in keydate with this code:
> keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value), 1)
>
> keydate is a variable decalred as a DATE type
> DATESERIAL(year,month,day) is a function for creating dates eg
> DATESERIAL(2009,7,15)
>
> then we set off using each cell from B2 and go down the column using a loop
> Target is a variable declared as a range, an object, and we initially set it
> to cell B2, before we end the loop, we move it down the column by setting it
> to the next cell below using the OFFSET method
> SET Target = Target.Offset(1)
>
> now Targets date is set to the 1st of the month and we compare this to the
> keydate and the month after the key date - we set to the first to avoid
> issues with day counts
> if the date in Target isn't one of these two dates, we don't want it, so we
> can delete the row
>
>
> "MSchmidty2" <(E-Mail Removed)> wrote in message
> news:88C407BB-09F7-4F82-A12B-(E-Mail Removed)...
> > Thanks for the reply and your help, Patrick. I'm a little rusty with VBA
> > and
> > this code is definitely over my head. I receive a run-time error 13 'Type
> > Mismatch' when I attempt to run this macro, and I've tried to determine
> > why,
> > but have been unsuccesful.
> >
> > "Patrick Molloy" wrote:
> >
> >> Sub tester()
> >>
> >>
> >> Dim keydate As Date
> >> Dim yr As Long
> >> Dim target As Range
> >> Dim bremove As Boolean
> >>
> >> 'initialise date
> >> keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value),
> >> 1)
> >>
> >>
> >> Set target = Range("B2")
> >> Do Until target.Value = ""
> >> bremove = True
> >> 'check this month
> >> If DateSerial(Year(target.Value), Month(target.Value), 1) = keydate
> >> Then
> >> bremove = False
> >> 'check next month
> >> ElseIf DateSerial(Year(target.Value), Month(target.Value) - 1, 1) =
> >> keydate Then
> >> bremove = False
> >> End If
> >> If bremove Then
> >> Rows(target.Row).Delete
> >> End If
> >>
> >> Set target = target.ffset(1)
> >> Loop
> >>
> >> End Sub
> >>
> >>
> >>
> >>
> >> "MSchmidty2" <(E-Mail Removed)> wrote in message
> >> news:1E2BD19B-A7EA-4520-8FB4-(E-Mail Removed)...
> >> > Hi,
> >> > I'm working on a chart which has a variety of job numbers in column a.
> >> > These job #'s are coupled with dates in column b. I need to check the
> >> > date
> >> > of the top cell in the column, and then keep the dates from the rest of
> >> > the
> >> > month from that date, and also the next month, while deleting the rest
> >> > of
> >> > the
> >> > job #'s and dates beyond that. I can't hide the rows because there is
> >> > more
> >> > information that is needed. Any tips would be appreciated. Thanks!
> >>
>
|
Similar Threads
