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

Rob,
Just to clarify your question:

Do you want to match the date in your first sheet with the two dates ( in
Cols B and E)i n the second sheet and then place the data from sheet one with
the data which has the closest match?

So data would go into Cols C/D if the date was closest to that in col B or
F/G if closest to the date in col E.

In your example if the date in sheet one was 19/01/2005 the resuilts would
be in cols C/D - correct?

If so, calculate Date differences (Sheet1Date - sheet2Date1) and
(Sheet1Date-Sheet2Date2) and find smallest. Use these differences to detrmine
where to put your data.

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

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

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



.

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



.

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



.


.

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



.


.

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



.


.


.

Yes

"Rob" wrote:

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



.


.


.