A B C
Day Month Year
21 June 2005

assume the 21 is in A5 on Sheet1

the subscript out of range error would be caused if you didn't have a
worksheet named Sheet2 or you didn't have a worksheet named sheet2 or if you
did have a worksheet named sheet1, it didn't contain a listbox from the
control toolbox toolbar with a name of Listbox1. The code worked fine for
me with those names.

If you you objects don't have those names, correct the code to reflect the
names you have.


Sub ABC()
Dim dt As Date
Dim sh As Worksheet
Dim tot As Double
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim lb As MSForms.ListBox
Dim cnt As Long
set sh = Worksheets("Sheet1")
dt = DateValue(sh1.Cells(5,"B") & " " & sh1.Cells(5,"A") & ", " & _
sh1.Cells(5,"C"))

With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

Set lb = sh.OLEObjects("Listbox1").Object
Set rng1 = sh.OLEObjects("Listbox1").BottomRightCell
' assume dates on sheet2 are in column 4
tot = 0
lb.Clear
For Each cell In rng
If cell.Offset(0, 3) < dt Then
lb.AddItem cell
cnt = lb.ListCount - 1
lb.List(cnt, 1) = cell.Offset(0, 1)
lb.List(cnt, 2) = cell.Offset(0, 2)
lb.List(cnt, 3) = cell.Offset(0, 3).Text
lb.List(cnt, 4) = cell.Offset(0, 4)
tot = tot + cell.Offset(0, 4).Value
End If
Next
rng1.Offset(1, 0).Value = tot
End Sub

--
Regards,
Tom Ogilvy


"A.G.M ash" wrote in message
...
hi

thanks for your reply your amazing

and im thick

I have tried it and I keep getting runtime error '9': subscript out of
range.
also the date is not in one column there is individual columns for dd mm
as
text (i.e august) and yyyy


like this

A B C
Day Month Year
21 June 2005

can you tell me how to change the program to accomadate this format
instead
of using dt as Date


and how do I use drop dwon boxes to choose the filter. I'm thinkin use a
linked cell for a dropdown box to put in the value and then refer dt say
to
sheetX.Cells("1","a")

"Tom Ogilvy" wrote:

there was a typo:

Set rng1.Offset(1, 0).Value = tot


should be

rng1.Offset(1, 0).Value = tot

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Sub ABC()
Dim dt As Date
Dim sh As Worksheet
Dim tot As Double
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim lb As MSForms.ListBox
Dim cnt As Long
dt = DateValue("1/5/2007")

With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

Set sh = Worksheets("Sheet1")
Set lb = sh.OLEObjects("Listbox1").Object
Set rng1 = sh.OLEObjects("Listbox1").BottomRightCell
' assume dates on sheet2 are in column 4
tot = 0
lb.Clear
For Each cell In rng
If cell.Offset(0, 3) < dt Then
lb.AddItem cell
cnt = lb.ListCount - 1
lb.List(cnt, 1) = cell.Offset(0, 1)
lb.List(cnt, 2) = cell.Offset(0, 2)
lb.List(cnt, 3) = cell.Offset(0, 3).Text
lb.List(cnt, 4) = cell.Offset(0, 4)
tot = tot + cell.Offset(0, 4).Value
End If
Next
Set rng1.Offset(1, 0).Value = tot
End Sub


You can't change the appearance of individual elements/cells within the
listbox. All must have the same format. To the best of my knowledge,
borders are not supported.

--
Regards,
Tom Ogilvy


"A.G.M ash" wrote:

Hi all you genius programmers

I have a question as you probably guest.

I am making a workbook in excel for my income and outgoings. I am
trying to
make the data for my outgoings which is located on sheet2 and spans
across
several columns appear in sheet1 but according to a selected month or
dd/mm
or dd/mm/yy or mm/yy or yy similar to a normal filter but it will be
selected
by drop down boxes. I have tried to simplify the calling of the above
by
using seperate columns for dd mm and yy of each transaction. I havnt
made any
dropdowns or anything like that yet as I want the data to apear in
the centre
of sheet1 and to only take up x amount of rows and be scrollable in
order to
see the other info. I have tried using a list box so far instead of
the cells
to dispaly the data wich works fine only it obviously displays the
range that
i manually put in its properties. so Is there a way to use the
dropdown boxes
in order to tell the list box what filtered data to view. also can
you format
the data in a list box to have borders and cells some headings bold
and some
cells with a bg colour. what would be ace was if i could just apply
the
format to sheet2 and the list box just shows it exactly as it is
formatated
on sheet2. or is there another way of doing all this. and one other
thing i
would like to is add all the values in the filtered range to give the
total
balance and display that in the bootom line of the list box.

It doesnt have to be a list box though it can be anything that allows
me to
achieve this result.

many many many many thanks in advance

Ash Mokhberi

Looks like you ignored the meat of the story so I am not going to guess
where your data is. If you want to send me a sample workbook with your
dropdowns and your data and your listbox and my last code and what you want,
this can be cleared up in a few minutes I would think.




--
Regards,
Tom Ogilvy

"A.G.M ash" wrote in message
...
you seem to have slightly missunderstood what I meant

sheet2
A B C
Day Month Year
21 June 2005

is the location of the data to be filtered not the entry of the filter
parameters

and I want to be able to filter by

either day month or year individualy or as a group so

one filter might be to filter by june so the search will lookup all the
values of june from the data in

sheet 2

A B C
Day Month Year
[ 21 June 2005 ] it will then show
[ 18 June 2006 ] these two results in the
listbox
18 november 1985


but then I can also choose 21 june so it will only show

A B C
Day Month Year
[ 21 June 2005 ] this in the list box
18 June 2006
18 november 1985

I tried modifieng your code but I am a novice so didnt work but maybe you
can help.

it worked for only looking up one cell value I.E lookup june in colymn b
I tried to change it to lookup several columns but it didnt work.

I have ignored the dt = it is still there but not used

I think I am only getting it wrong with the Set rng

here it is

Sub ABC()
Dim dt As Date
Dim sh As Worksheet
Dim tot As Double
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim lb As MSForms.ListBox
Dim cnt As Long
dt = DateValue("01/05/2007")
' ignore this i am not using it

With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 3).End(xlDown))

End With

Set sh = Worksheets("Sheet1")
Set lb = sh.OLEObjects("Listbox1").Object
Set rng1 = sh.OLEObjects("Listbox1").BottomRightCell

tot = 0
lb.Clear
For Each cell In rng
If cell.Offset(0, 1) = Sheet3.Cells("1", "a") And cell.Offset(0, 2) =
Sheet3.Cells("1", "b") And cell.Offset(0, 3) = Sheet3.Cells("1", "c") Then

lb.AddItem cell
cnt = lb.ListCount - 1
lb.List(cnt, 1) = cell.Offset(0, 1)
lb.List(cnt, 2) = cell.Offset(0, 2)
lb.List(cnt, 3) = cell.Offset(0, 3)
lb.List(cnt, 4) = cell.Offset(0, 4)
lb.List(cnt, 5) = cell.Offset(0, 5)
lb.List(cnt, 6) = cell.Offset(0, 6)
tot = tot + cell.Offset(0, 5).Value

Else
MsgBox "no match"
' also this pops up for every cell it looks at that doesnt match

End If

Next

End Sub



"Tom Ogilvy" wrote:

A B C
Day Month Year
21 June 2005

assume the 21 is in A5 on Sheet1

the subscript out of range error would be caused if you didn't have a
worksheet named Sheet2 or you didn't have a worksheet named sheet2 or if
you
did have a worksheet named sheet1, it didn't contain a listbox from the
control toolbox toolbar with a name of Listbox1. The code worked fine
for
me with those names.

If you you objects don't have those names, correct the code to reflect
the
names you have.


Sub ABC()
Dim dt As Date
Dim sh As Worksheet
Dim tot As Double
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim lb As MSForms.ListBox
Dim cnt As Long
set sh = Worksheets("Sheet1")
dt = DateValue(sh1.Cells(5,"B") & " " & sh1.Cells(5,"A") & ", " & _
sh1.Cells(5,"C"))

With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

Set lb = sh.OLEObjects("Listbox1").Object
Set rng1 = sh.OLEObjects("Listbox1").BottomRightCell
' assume dates on sheet2 are in column 4
tot = 0
lb.Clear
For Each cell In rng
If cell.Offset(0, 3) < dt Then
lb.AddItem cell
cnt = lb.ListCount - 1
lb.List(cnt, 1) = cell.Offset(0, 1)
lb.List(cnt, 2) = cell.Offset(0, 2)
lb.List(cnt, 3) = cell.Offset(0, 3).Text
lb.List(cnt, 4) = cell.Offset(0, 4)
tot = tot + cell.Offset(0, 4).Value
End If
Next
rng1.Offset(1, 0).Value = tot
End Sub

--
Regards,
Tom Ogilvy


"A.G.M ash" wrote in message
...
hi

thanks for your reply your amazing

and im thick

I have tried it and I keep getting runtime error '9': subscript out of
range.
also the date is not in one column there is individual columns for dd
mm
as
text (i.e august) and yyyy


like this

A B C
Day Month Year
21 June 2005

can you tell me how to change the program to accomadate this format
instead
of using dt as Date


and how do I use drop dwon boxes to choose the filter. I'm thinkin use
a
linked cell for a dropdown box to put in the value and then refer dt
say
to
sheetX.Cells("1","a")

"Tom Ogilvy" wrote:

there was a typo:

Set rng1.Offset(1, 0).Value = tot


should be

rng1.Offset(1, 0).Value = tot

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Sub ABC()
Dim dt As Date
Dim sh As Worksheet
Dim tot As Double
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim lb As MSForms.ListBox
Dim cnt As Long
dt = DateValue("1/5/2007")

With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

Set sh = Worksheets("Sheet1")
Set lb = sh.OLEObjects("Listbox1").Object
Set rng1 = sh.OLEObjects("Listbox1").BottomRightCell
' assume dates on sheet2 are in column 4
tot = 0
lb.Clear
For Each cell In rng
If cell.Offset(0, 3) < dt Then
lb.AddItem cell
cnt = lb.ListCount - 1
lb.List(cnt, 1) = cell.Offset(0, 1)
lb.List(cnt, 2) = cell.Offset(0, 2)
lb.List(cnt, 3) = cell.Offset(0, 3).Text
lb.List(cnt, 4) = cell.Offset(0, 4)
tot = tot + cell.Offset(0, 4).Value
End If
Next
Set rng1.Offset(1, 0).Value = tot
End Sub


You can't change the appearance of individual elements/cells within
the
listbox. All must have the same format. To the best of my
knowledge,
borders are not supported.

--
Regards,
Tom Ogilvy


"A.G.M ash" wrote:

Hi all you genius programmers

I have a question as you probably guest.

I am making a workbook in excel for my income and outgoings. I am
trying to
make the data for my outgoings which is located on sheet2 and
spans
across
several columns appear in sheet1 but according to a selected month
or
dd/mm
or dd/mm/yy or mm/yy or yy similar to a normal filter but it will
be
selected
by drop down boxes. I have tried to simplify the calling of the
above
by
using seperate columns for dd mm and yy of each transaction. I
havnt
made any
dropdowns or anything like that yet as I want the data to apear in
the centre
of sheet1 and to only take up x amount of rows and be scrollable
in
order to
see the other info. I have tried using a list box so far instead
of
the cells
to dispaly the data wich works fine only it obviously displays the
range that
i manually put in its properties. so Is there a way to use the
dropdown boxes
in order to tell the list box what filtered data to view. also can
you format
the data in a list box to have borders and cells some headings
bold
and some
cells with a bg colour. what would be ace was if i could just
apply
the
format to sheet2 and the list box just shows it exactly as it is
formatated
on sheet2. or is there another way of doing all this. and one
other
thing i
would like to is add all the values in the filtered range to give
the
total
balance and display that in the bootom line of the list box.

It doesnt have to be a list box though it can be anything that
allows
me to
achieve this result.

many many many many thanks in advance

Ash Mokhberi