RE: Data define and search function request?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Sub GetOutput()

Do
StartDate = InputBox("Enter Start Date : ")
Loop While Not IsDate(StartDate)
StartDate = DateValue(StartDate)

Do
EndDate = InputBox("Enter End Date : ")
Loop While Not IsDate(EndDate)
EndDate = DateValue(EndDate)

If StartDate > EndDate Then
MsgBox ("Exiting Macro - End Date is before Start Date")
Exit Sub
End If

With Sheets("Output")
If .Range("A1") = "" Then
.Range("A1") = Date
.Range("B1") = Code
.Range("C1") = Quantity
.Range("D1") = Value
OutputRow = 2
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
OutputRow = LastRow + 1
End If
End With


With Sheets("Codes")
CodeRow = 1
Do While .Range("A" & CodeRow) <> ""
Code = .Range("A" & CodeRow)

With Sheets("Data")
Set c = .Columns("B").Find(what:=Code, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
Date = c.Offset(0, -1)
If Date >= StartDate And Date <= EndDate Then
.Range("A" & c.Row & ":D" & c.Row).Copy _
Destination:=Sheets("Output").Range("A" & OutputRow)
OutputRow = OutputRow + 1
End If
Set c = .Columns("B").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAddr
End If

End With
CodeRow = CodeRow + 1
Loop
End With
End Sub


"joecrabtree" wrote:

All,

I have posted this already, but still have had no joy... and takers??

Thanks....

I have a workbook with three (3) worksheets in it. The first one is
called 'codes' the second one is called 'data', and the third one is
called 'output'.

In 'codes' there is a list of codes that appear in the data set for
example:

CODES:
ABC
BGD
JDJ
KDK
DID

In the sheet 'data' the data is in the following format:

DATE CODE QUANTITY VALUE
01/01/09 ABC 10 1000
01/01/09 ABC 1 100
01/01/09 KDK 4 44
02/01/09 JDJ 2 13
02/01/09 JDJ 33 22

What I would like to be able to do is as follows:

1) The user is asked to input a date range for example
01/01/09-01/01/09
2) The program then searchs the data for everything in that date range
in the 'data' sheet that corresponds to the codes defined in the
'codes' sheet and returns the summed values. For example when the user
enters in 01/01/09-01/01/09 the value returned would be:

CODE QUANTITY VALUE
ABC 11 1100

3) This data would be displayed in the 'output' worksheet.
4) If the User entered the date range 01/01/09-02/01/09 then the
following values would be returned:

DATE CODE QUANTITY VALUE
01/01/09 ABC 11 1100
01/01/09 KDK 4 44
02/01/09 JDJ 35 35

If anyone could help it would be much appreciated,

Regards

Joseph Crabtree

.

Relevant Pages

  • Re: Searching into Non-Contiguous ranges in a columns
    ... slr = Cells.End.Row ... Set r = .Cells ... Loop While Not c Is Nothing And c.Address firstAddress ... I know my codes are from A5 to A635. ...
    (microsoft.public.excel.programming)
  • RE: Timing events in GNAT GPL 2006
    ... loop null; end loop; ... There are three things wrong with your codes. ... The protected procedure Inc_Clock has wrong parameter mode. ... non-local pointer cannot point to local object error at ...
    (comp.lang.ada)
  • Re: Sun Studio Express 3 compilers available for download
    ... in subroutines on arrays with passed in dimensions. ... The compiler has ... And F77 codes are ... need to worry about loop order. ...
    (comp.lang.fortran)
  • Re: Turbo Assembler question
    ... loop LDA message,x ... The 0 at the end of the string causes the loop to exit at the BEQ ... use various color codes, cursor movements, etc. by giving their PETSCII codes ... "There are some things in life worth obsessing over. ...
    (comp.sys.cbm)
  • Re: Data define and search function request?
    ... I have a workbook with three worksheets in it. ... called 'codes' the second one is called 'data', ... In the sheet 'data' the data is in the following format: ... DATE CODE QUANTITY VALUE ...
    (microsoft.public.excel.programming)