Re: date code error, help would be appreciated!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Norman,

No luck with recording the auto-filter,
do you think you could have a stab at the code?

if you can give me a little push towards the right direction,
i may be able to figure the rest out for myself.
thanks again

mike

"Norman Jones" wrote:

> Hi Mike,
>
> > Norman can Autofilter be used in macros?
>
> Certainly!
>
> Turn on the macro recorder, perform the required operations manually. This
> will provide you with base code that can be edited to improve efficiency and
> render it appropriate for general application.
>
> If you require any assistance with such editing, post back with specifics.
>
> From your description, I think that the autofilter will best meet your
> requirements.
>
> ---
> Regards,
> Norman
>
>
>
> "mike" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:200CDD80-61F9-424C-9F3C-03C3DE07C608@xxxxxxxxxxxxxxxx
> > Norman can Autofilter be used in macros?
> >
> > There is data in other columns, but i need only 2 other columns at most.
> > the rows are sorted by date order but are not sequential, eg the dates
> > will
> > run in month order, but days may be skipped or doubled up, where there
> > were
> > no entries for some days or more on another day.
> > i am trying to format it in a macro, because i have wrote something
> > similar,
> > using your help before, which looks for a certain word in column a then
> > copies all information relating to that word, ie all rows that contain the
> > word, and the relevant information in those rows, to a new ***, which is
> > then automatically mailed to someone.
> >
> > what i am trying to do now is collate the number values to complile
> > reports
> > and email them automatically. this is just the start, my manager also
> > asked
> > if it would be possible to analyse the amounts on a quarter on quarter
> > basis
> > to see if there is a reduction in frequency of the values.
> >
> > i hope you can help, i think im getting a bit out of my depth!! might have
> > to go and buy a textbook for this stuff at lunch!!!
> >
> > thanks again
> >
> > mike
> >
> > "Norman Jones" wrote:
> >
> >> Hi Mike,
> >>
> >> Are the rows sorted in date order?
> >>
> >> Is there additional data in columns C ==> that should be copied?
> >>
> >> If your purpose is to extract information for a one year period, why not
> >> use
> >> the autofilter feature to extract all rows between your required dates.
> >> You
> >> could then use the SubTotal function to sum the filtered rows, e.g.:
> >>
> >> =SUBTOTAL(9,B:B)
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "mike" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:59E7A0BB-768C-4867-95E7-A2AFB5E3B467@xxxxxxxxxxxxxxxx
> >> > Norman,
> >> >
> >> > you are correct.
> >> > it is the formula you provided.
> >> >
> >> > basically, i have a ***.
> >> > it contains a number of columns.
> >> > my overall purpose of the macro is to.....
> >> >
> >> > search in column A for all dates that are up to and including one year
> >> > ago
> >> > from today's date.
> >> > then in column B there is an amount value.
> >> > i wish to then add the amounts in column from today back to a year ago
> >> > today.
> >> > if it easier to copy them to a new *** then this would suffice.
> >> > i hope you can help.
> >> > thanks
> >> >
> >> > mike
> >> > i wish to add the amount value th
> >> >
> >> > "Norman Jones" wrote:
> >> >
> >> >> Hi Mike,
> >> >>
> >> >> I believe that the code you are trying to use was originally suggested
> >> >> by
> >> >> me
> >> >> for a very different purpose: to copy rows containing one of several
> >> >> words
> >> >> supplied by a user in response to an input box.Certainly, as used, the
> >> >> code
> >> >> is inappropriate for your current purpose.
> >> >>
> >> >> In order to assist you, you will need to explain the layout of your
> >> >> data
> >> >> and
> >> >> your purpose: is the data sorted sequentially; are all rows older than
> >> >> 1
> >> >> year to be copied; is data to be appended to existing data on the
> >> >> target
> >> >> ***, or is the existing data to be overwritten?
> >> >>
> >> >>
> >> >> ---
> >> >> Regards,
> >> >> Norman
> >> >>
> >> >>
> >> >>
> >> >> "mike" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:BD196953-413A-4281-9576-EB16741FF022@xxxxxxxxxxxxxxxx
> >> >> > morning,
> >> >> >
> >> >> > i having some issues with the below code.
> >> >> > it is supposed to search for the date today minus 12 months, and
> >> >> > then
> >> >> > copy
> >> >> > all rows in between to a new ***.
> >> >> > any suggestions would be greatly appreciated!
> >> >> > thanks very much on this very cold and foggy morning!!
> >> >> > mike
> >> >> >
> >> >> > Private Sub CommandButton2_Click()
> >> >> > Dim Rng As range
> >> >> > Dim rCell As range
> >> >> > Dim copyRng As range
> >> >> > Dim destRng As range
> >> >> > Dim mydate As Date
> >> >> > Dim sh As Work***
> >> >> > Dim CalcMode As Long
> >> >> > Dim arr As Variant
> >> >> > Dim res As Variant
> >> >> > Dim mymonth As Date
> >> >> >
> >> >> >
> >> >> > mydate = Date
> >> >> > mymonth = Month(mydate)
> >> >> > Set sh = Sheets("Sheet 1")
> >> >> > Set Rng = sh.range("A5:A100")
> >> >> > Set destRng = Sheets("Sheet 2").range("A2")
> >> >> >
> >> >> > res = mydate - mymonth
> >> >> >
> >> >> > If res = "" Then Exit Sub
> >> >> > arr = Split(res, " ")
> >> >> >
> >> >> > With application
> >> >> > CalcMode = .Calculation
> >> >> > .Calculation = xlCalculationManual
> >> >> > .ScreenUpdating = False
> >> >> > End With
> >> >> >
> >> >> >
> >> >> > For Each rCell In Rng.Cells
> >> >> > If Not IsError(application.Match(rCell.Value, arr, 0)) Then
> >> >> > If copyRng Is Nothing Then
> >> >> > Set copyRng = rCell
> >> >> > Else
> >> >> > Set copyRng = Union(rCell, copyRng)
> >> >> > End If
> >> >> > End If
> >> >> > Next rCell
> >> >> >
> >> >> > If Not copyRng Is Nothing Then
> >> >> > copyRng.EntireRow.Copy Destination:=destRng
> >> >> > Else
> >> >> > 'nothing found, do nothing
> >> >> > End If
> >> >> >
> >> >> > With application
> >> >> > .Calculation = CalcMode
> >> >> > .ScreenUpdating = True
> >> >> > End With
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Quantcast