Excel Macro help needed

kaisersose77

It's being a good few years since I've done any programming/excel work and the answer to this is probably stupid!

Basically, I have 2 sheets in an excel file. The first one (Results) is full of data with the first column containing dates. And each date is entered 3 times

Date John Joe

14/01/2008 6 7
14/01/2008 7 6
14/01/2008 9 3
15/01/2008 4 3
15/01/2008 5 4
15/01/2008 2 5

So the first sheet continues on like that 3 times for each date.

What i want to do is have a macro that as you to enter a certain date and then it copies all the data for this date onto the second sheet. Where i can get totals etc for that particular date. So searching 14/01/2008 would copy this data the second sheet:

Date John Joe

14/01/2008 6 7
14/01/2008 7 6
14/01/2008 9 3

When I done this program first, I had week instead of date, and had 51,52 etc. And that worked fine, but now that I am using date, it's not working. I assume the problem is got to do with strings or something.

This is the macro

Sub BackgroundColors()
'Set Results worksheet active
Worksheets("Results").Activate

'Clear Report Sheet
Worksheets("Reports").Range("a2:n25").Value = ""

'Declare Row and column variables and initialize
Dim ReportRow As Integer ' Row counter for Report Sheet
ReportRow = 2 ' Set to 2 to allow for header row

Dim Row As Integer ' Row for Results Sheet
Dim Col As Integer ' Column for Results Sheet and Report Sheet
Row = 0
Col = 1

'Enter Results Number
Message = "Enter the Results number required" ' Set prompt.
Title = "Results Summary" ' Set title.
'Default = Range("a2") ' Set default.
' Display message, title, and default value.
Result = InputBox(Message, Title, Default)

'Search thru 1st column for matching part numbers and copy valid cells to
'Reports sheet
For Each cell In Range("a1:a9000")

If Not IsError(cell.Value) Then
Row = Row + 1

With cell.Interior

Select Case cell.Value


Case Is = Int(Result)

For Col = 1 To 14
Worksheets("Reports").Cells(ReportRow, Col) = Worksheets("Results").Cells(Row, Col)
Next Col

ReportRow = ReportRow + 1

Case Else

End Select

End With

Else

cell.Interior.ColorIndex = xlAutomatic
MsgBox ("The Item " + Result + " Is not Listed")

End If

Next cell

Worksheets("Reports").Activate

MsgBox ".... REPORT READY....." + Chr$(13) + "Please check that all rows have " + Chr$(13) + "valid data before Printing"

End Sub




Searching a date instead of a number like 51, highligts this in the code above

Case Is = Int(Result)


Anyone know what i need to change?

Beano

take out the call to int. The result variable contains a date not an int. just use

Case Is = Result

or drop the case totally and just use

if cell.Value = result then

kaisersose77

cheers

Case Is = Result worked fine

I think I need to start reading up on this stuff again

thanks again