Thanks Toppers,

I have added the code to my sheet and now I have another
error.

Can I email you the files to take a quick look if thats
ok they only contain a few lines of sample data (50kb)
total.

Is that ok?

Rob
-----Original Message-----
Try this:

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, DateRng As Range,
Mindiff As Long
Dim ColCount As Integer, c As Integer, Startcol As
Integer, endCol As Integer
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


Startcol = Wsout.Cells(1, "J").Column
endCol = Wsout.Cells(1, "At").Column
Mindiff = 999999

For c = Startcol To endCol Step 3
If Abs(InDate - Wsout.Cells(DestRow, c).Value) <
Mindiff Then
Mindiff = Abs(InDate - Wsout.Cells(DestRow,
c).Value)
DestCol = c + 1
End If
Next



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

End Sub

" wrote:

They are dates then two empty cols then a date then 2
empty cols etc all different dates some rows have a
range
between J and AT of 1 year some of 2.

Strange I know but if you imagine

Date Blank Blank Date Blank Blank Date Blank Blank

-----Original Message-----
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



.


.


.