About Us

Toppers

Ignore last post - reply to a completely different posting!!!

"Toppers" wrote:

Hi,

Try this which assumes data starts in column 1

HTH

Sub CreateRanges()

Dim Lastrow As Long
Dim Lastcol As Integer
Dim Col As Integer
Dim ws1 As Worksheet

Dim AgeSeries As Range
Dim NameSeries As Range
' Add other ranges as required .....

Set ws1 = Worksheets("Sheet2")

With ws1
Lastcol = .Cells(6, Columns.Count).End(xlToLeft).Column
Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

For Col = 1 To Lastcol

Select Case .Cells(6, Col)
'if the name is age, then set the range to the stuff below
Case "Age"
Set AgeSeries = .Range(.Cells(7, Col), .Cells(Lastrow,
Col))
Case "Name"
Set NameSeries = .Range(.Cells(7, Col), .Cells(Lastrow,
Col))
Case ("Date")
' ...
End Select

Next Col
End With
End Sub

"mike" wrote:

Toppers,

Not possible, I am at work, and cannot send external email, nor access
webmail.
Any more code suggestions?

thanks

mike

"Toppers" wrote:

Mike,
Suggests data which cannot be converted to an integer - what is
in the Cells(srow,1)?

If you prefer, mail your spreadsheet to me so I can check it through as it
can be quicker than communicating via the news group.

My e-mail is:

"mike" wrote:

Toppers,

There is a type mismatch error in your code on this line...

If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1

any suggestions

mike

"Toppers" wrote:

Mike,
Does this meet you need? It assumes data is sorted in ascending
date sequence and finds a start date = to your start date and end date <= to
TODAY.
It sums Column B between these rows.

HTH

Sub A()

Dim StartDate As Long, EndDate As Long
Dim Btotal As Double

EndDate = CLng(Now())
StartDate = EndDate - 365
' Above dates will be 09/12/04 to 09/12/05 inclusive (UK date format!)
srow = Application.Match(StartDate, Range("a2:a2000"), 1)
' If "Match" date less then StartDate then begin at next date ( StartDate)
If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1
' Date will be less than or equal to Today's date
erow = Application.Match(EndDate, Range("a2:a2000"), 1)
'Total data in column B
Btotal = Application.Sum(Range("B" & srow & ":B" & erow))

End Sub

"mike" wrote:

Norman,
this is the code below,
i did what i normally do....
select row, data, autofilter.
but the arrows didnt show.

also, i am a little confused now, how i can filter between today and a year
ago today using filter.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 09/12/2005 by mike
'

'
Rows("4:4").Select
Selection.autofilter
range("A4").Select
End Sub

thanks
mike

"Norman Jones" wrote:

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" wrote in message
...
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" wrote in message
...
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 sheet, 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" wrote in message
...
Norman,

you are correct.
it is the formula you provided.

basically, i have a sheet.
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 sheet 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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Any Help Appreciated	CALM1	Excel Worksheet Functions	3	June 17th 11 06:59 AM
Help is much appreciated for-	V	Excel Discussion (Misc queries)	10	May 28th 09 12:16 AM
Help would be much appreciated!	Daptin	Excel Worksheet Functions	2	November 24th 06 03:50 AM
Any help much appreciated	Ryk	Excel Discussion (Misc queries)	0	July 31st 06 04:59 PM
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste)	Tim[_36_]	Excel Programming	4	April 23rd 04 02:53 AM

Menu

About Us