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.

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.

Thanks a lot! 1 question. How do I get the "inpData =
Sheets("Input").Range("B2").Value" to recognise a range or cells? as in
B2:C20. When I try to input B2:C20, it gives this error "Run-time error '13'
: Type mismatch". Any thoughts? Thanks


"Rowan Drummond" wrote:

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.

You need to explain more about your data layout. Is the input data in
the range B2:C20 related to a single date and if so what cell is that
date in? Or are there multiple dates on the input data sheet? What I
need to know is the exact layout of the Inupt and Data sheets.

Regards
Rowan

PS Please keep all correspondence in the newsgroup.

Agasnine wrote:
Thanks a lot! 1 question. How do I get the "inpData =
Sheets("Input").Range("B2").Value" to recognise a range or cells? as in
B2:C20. When I try to input B2:C20, it gives this error "Run-time error '13'
: Type mismatch". Any thoughts? Thanks


"Rowan Drummond" wrote:


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.

This is how the page is layed out.
the input sheet has:
A date entered into A2
Data entered into a range of cells (B2:C20)
All of the data entered into cells B2:C20 is related to the date
entered into A2.
At the end of the week the data and date are changed to the new data
The Main sheet has:
A series of weekending dates (for the entire year) all on row 4
The data from teh input sheet (B2:C20) needs to be pasted one cell
down and two
cells left. (FYI the reason they are pasted there is because
those cells are
actually hidden because they are just used in a formula to get
a percentage
that is in the cells directly below the date.

I found a macro that worked, I just wanted to make it clear what I was
looking for. Your macro would have worked if I was looking to take one cell
and move it to the Main page. Unfortunatly I was looking to move all of the
cells in B2:C20.

This is the macro that ended up working.

Sub CopyDataBlock()
Dim dt As Date, res As Variant
Dim rng As Range
'Take a specified date from cell A2 on page "Input"
dt = Sheets("Input").Range("A2").Value
' find that date on page "Main".
' all dates on "Reports" are in row 4.
res = Application.Match(CLng(dt), _
Sheets("Main").Rows(4), 0)
If Not IsError(res) Then
'copy B2:C20 to a cell range that
'starts Left 2, Down 1
Set rng = Sheets("Main").Cells(5, res - 2)
Sheets("Input").Range("B6:C20").Copy Destination:=rng
End If
End Sub

Thanks for the help with this macro. I had been racking my brain for hours
on end trying to figure out how to do this. Your promt responce (24 minutes)
to my first post put faith in this discussion group which caused me to
continue to use this. Thanks for all the help.
-Ryan



"Rowan Drummond" wrote:

You need to explain more about your data layout. Is the input data in
the range B2:C20 related to a single date and if so what cell is that
date in? Or are there multiple dates on the input data sheet? What I
need to know is the exact layout of the Inupt and Data sheets.

Regards
Rowan

PS Please keep all correspondence in the newsgroup.

Agasnine wrote:
Thanks a lot! 1 question. How do I get the "inpData =
Sheets("Input").Range("B2").Value" to recognise a range or cells? as in
B2:C20. When I try to input B2:C20, it gives this error "Run-time error '13'
: Type mismatch". Any thoughts? Thanks


"Rowan Drummond" wrote:


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.

Ryan

I'm glad you found a solution.

Regards
Rowan

Agasnine wrote:
This is how the page is layed out.
the input sheet has:
A date entered into A2
Data entered into a range of cells (B2:C20)
All of the data entered into cells B2:C20 is related to the date
entered into A2.
At the end of the week the data and date are changed to the new data
The Main sheet has:
A series of weekending dates (for the entire year) all on row 4
The data from teh input sheet (B2:C20) needs to be pasted one cell
down and two
cells left. (FYI the reason they are pasted there is because
those cells are
actually hidden because they are just used in a formula to get
a percentage
that is in the cells directly below the date.

I found a macro that worked, I just wanted to make it clear what I was
looking for. Your macro would have worked if I was looking to take one cell
and move it to the Main page. Unfortunatly I was looking to move all of the
cells in B2:C20.

This is the macro that ended up working.

Sub CopyDataBlock()
Dim dt As Date, res As Variant
Dim rng As Range
'Take a specified date from cell A2 on page "Input"
dt = Sheets("Input").Range("A2").Value
' find that date on page "Main".
' all dates on "Reports" are in row 4.
res = Application.Match(CLng(dt), _
Sheets("Main").Rows(4), 0)
If Not IsError(res) Then
'copy B2:C20 to a cell range that
'starts Left 2, Down 1
Set rng = Sheets("Main").Cells(5, res - 2)
Sheets("Input").Range("B6:C20").Copy Destination:=rng
End If
End Sub

Thanks for the help with this macro. I had been racking my brain for hours
on end trying to figure out how to do this. Your promt responce (24 minutes)
to my first post put faith in this discussion group which caused me to
continue to use this. Thanks for all the help.
-Ryan