Rob,
Are columns between J and AT dates?

"Rob" wrote:

Thanks toppers,

I have checked the code in my worksheet and it works to
the point it says data overflow.

The date to match is between columns J and AT in the
sheet so is it possible to say something like look in
DestRow between J and AT for the nearest date

Thanks again.

Rob
-----Original Message-----
Some Code:


Sub Test()
' Assumes Data in row 2 (Reference, Date and Value)
Call DateDiff(Cells(2, 1), Cells(2, 2), Cells(2, 3))
End Sub
---------------------------------------------------------
--------------------------------------
Sub DateDiff(InRef, InDate, Invalue)

Dim DestCol As Integer, DestRow As Long, Diff1 As
Integer, Diff2 As Integer
Dim OutRng As Range, RefCell As Range
Dim Wsout As Worksheet

Set Wsout = Worksheets("Sheet2")


Set RefCell = Wsout.Range("A:A").Find(InRef) ' Find
Reference
DestRow =
RefCell.Row ' Set Row
of found
reference

Diff1 = Abs(InDate - Wsout.Cells(DestRow, 2))
Diff2 = Abs(InDate - Wsout.Cells(DestRow, 5))

If Diff1 < Diff2 Then
DestCol = 3
Else
DestCol = 6
End If

Set OutRng = Worksheets("Sheet2").Cells(DestRow, DestCol)
OutRng = InDate
OutRng.Offset(0, 1) = Invalue

End Sub
---------------------------------------------------------
--------------------------------------
HTH


"Rob" wrote:

Hi I have two spreadsheets, if both are open and the
first one contains 3 columns.

The first column is a reference the second one is an
amount and the third is a date.

The second spreadsheet has a column which the
reference
will match. There will only be one match in the list.

Then I will have my row number. The amount and the
date
then need entering on the row with the matching ref in
spreadsheet number 2.

I'm not sure if its possible to enter them as I would
like though.

Imagine the row contains data like the following in
the
second sheet-

Col A Col B Col C Col D Col E Col F Col
G
Ref match 17/01/2005 17/02/2005

So if the first sheet had data like this

Col A Col B Col C
Ref match 16/02/2005 52356

The ref would match so in the same line col f and g
would
be filled as below

Col A Col B Col C Col D Col E Col F
Col G
Ref match 17/01/2005 17/02/2005 16/02/2005
52356

It has gone there as it is nearer to the date
17/02/2005
than the date in B.

I hope I have explained it well enough for you to be
able
to help!

Thanks

Rob



.