Re: date code error, help would be appreciated!

Hi Mike,

> No luck with recording the auto-filter,

Post the code that you got when setting up the autofilter and I will try to
help.


---
Regards,
Norman



"mike" <mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:66F6546C-74DD-4BDC-92A9-BC20C1D5EA66@xxxxxxxxxxxxxxxx
> 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
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.