Problems with a finding macro and copying a range of cells

The following is a copy of a post that I did a few days ago, I am having a
problem using this macro with my data. The problem is that I need this to be
used with a range of data (ie. A1:B20). This macro can only be used (to my
understanding) with one "data" cell. Is there a macro that can function like
this but copy the range A1:B20 to the destination page? Or maybe this can be
used in a loop macro. I have never used a loop macro so I do not know how to
write one. Basically this is how it needs to go.
2 pages - Input and Main
Take a specified date from cell A4 on page "Input" and find that date on
page "Main". all dates on "Main" are in row 4.
Use the found date as a reference point and copy A1:B20 to a cell range that
starts Left 2, Down 1 from the reference point.

-OLD POST-

Assumptions:
On the input sheet the date is entered into Cell A2 and the data is
enterd into cell B2.
On the Main sheet the list of dates is in column A and the data should
be inserted into column B.

Sub finder()
Dim fDate As Date
Dim fndRng As Range
Dim inpData As String

fDate = Sheets("Input").Range("A2").Value
inpData = Sheets("Input").Range("B2").Value
With Sheets("Main").Columns(1)
Set fndRng = .Find(fDate)
End With
If Not fndRng Is Nothing Then
fndRng.Offset(0, 1).Value = inpData
End If
End Sub


Hope this helps
Rowan

Agasnine wrote:
> I am using Excel 2003 but need this to work in Excel '97.
> On a "Main" sheet I have a series of dates with data the is inserted below
> the dates. On an "Input" sheet I have a "data input" section with the date,
> and the data for that date.
> This is what I want to do.
>
> Input a date, type the data for that date, click a button to activate a
> macro or a series of macros. The macro would take the date from the "Input"
> sheet and find it on the "Main" sheet. Then it would take the data from the
> "Input" sheet and insert it on the "Main" sheet.
> The problem I am running into is getting the macro to "find" the date in the
> "Main" sheet and use that as a reference as to where to paste the data.

Sub CopyDataBlock()
Dim dt as Date, res as variant
Dim rng as Range
'Take a specified date from cell A4 on page "Input"
set dt = Worksheets("Input").Range("A4")
' find that date on page "Main".
' all dates on "Main" are in row 4.
res = application.Match(clng(dt), _
Worksheets("Main").Rows(4),0)
if not iserror(res) then
'copy A1:B20 to a cell range that
'starts Left 2, Down 1
set rng = worksheets("Main").Cells(5,res-2)
Worksheets("Input").Range("A1:B20").copy Destination:=rng
end if
End If


--
Regards,
Tom Ogilvy


"Agasnine" <[email protected]> wrote in message
news:[email protected]...
> The following is a copy of a post that I did a few days ago, I am having a
> problem using this macro with my data. The problem is that I need this to
be
> used with a range of data (ie. A1:B20). This macro can only be used (to
my
> understanding) with one "data" cell. Is there a macro that can function
like
> this but copy the range A1:B20 to the destination page? Or maybe this can
be
> used in a loop macro. I have never used a loop macro so I do not know how
to
> write one. Basically this is how it needs to go.
> 2 pages - Input and Main
> Take a specified date from cell A4 on page "Input" and find that date on
> page "Main". all dates on "Main" are in row 4.
> Use the found date as a reference point and copy A1:B20 to a cell range
that
> starts Left 2, Down 1 from the reference point.
>
> -OLD POST-
>
> Assumptions:
> On the input sheet the date is entered into Cell A2 and the data is
> enterd into cell B2.
> On the Main sheet the list of dates is in column A and the data should
> be inserted into column B.
>
> Sub finder()
> Dim fDate As Date
> Dim fndRng As Range
> Dim inpData As String
>
> fDate = Sheets("Input").Range("A2").Value
> inpData = Sheets("Input").Range("B2").Value
> With Sheets("Main").Columns(1)
> Set fndRng = .Find(fDate)
> End With
> If Not fndRng Is Nothing Then
> fndRng.Offset(0, 1).Value = inpData
> End If
> End Sub
>
>
> Hope this helps
> Rowan
>
> Agasnine wrote:
> > I am using Excel 2003 but need this to work in Excel '97.
> > On a "Main" sheet I have a series of dates with data the is inserted
below
> > the dates. On an "Input" sheet I have a "data input" section with the
date,
> > and the data for that date.
> > This is what I want to do.
> >
> > Input a date, type the data for that date, click a button to activate a
> > macro or a series of macros. The macro would take the date from the
"Input"
> > sheet and find it on the "Main" sheet. Then it would take the data from
the
> > "Input" sheet and insert it on the "Main" sheet.
> > The problem I am running into is getting the macro to "find" the date in
the
> > "Main" sheet and use that as a reference as to where to paste the data.