comparing data and write values if true
 

Hi
I have in sheet 1, all the dates of the year 2003 in column B
In sheet 2, i import data from another workbook using a macro
The data from the other workbook contains dates, and they are placed in the column B in sheet 2
If i.e. field B7 in sheet 2 is 13.11.2003, i want to write the values in cells c7,d7,e7,and f7 into the c,d,e,and f cells next to my match of date in column b,sheet1

Can anyone please help me with this

Jon-Henrik

comparing data and write values if true
 

Here is a non programming solution:

Sheet1 would contain VLOOKUP formulas

C5: =VLOOKUP($B5,sheet2!$B:$G,COLUMN()-1,0)
D5: =VLOOKUP($B5,sheet2!$B:$G,COLUMN()-1,0)
E5: =VLOOKUP($B5,sheet2!$B:$G,COLUMN()-1,0)
F5: =VLOOKUP($B5,sheet2!$B:$G,COLUMN()-1,0)
G5: =VLOOKUP($B5,sheet2!$B:$G,COLUMN()-1,0)

which allows you to use the fill handle, equivalent to

C5: =VLOOKUP($B5,sheet2!$B:$G,2,0)
D5: =VLOOKUP($B5,sheet2!$B:$G,3,0)
...

More information on VLOOKUP
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jon-Henrik" wrote =
I have in sheet 1, all the dates of the year 2003 in column B.
In sheet 2, i import data from another workbook using a macro.
The data from the other workbook contains dates, and they are placed in the column B in sheet 2.
If i.e. field B7 in sheet 2 is 13.11.2003, i want to write the values in cells c7,d7,e7,and f7 into the c,d,e,and f cells next to
my match of date in column b,sheet1.

comparing data and write values if true
 

Sub test()

Dim SheetRow As String
Dim rw As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim source As Range

Set ws2 = Worksheets("sheet2")
Set ws1 = Worksheets("sheet1")


For rw = 1 To ws2.Range("B5000").End(xlUp)
With ws2

SheetRow = CLng(Mid(Format$(CDate(.Cells
(rw, "B").Value), "yyy"), 3))

Set source = .Range(.Cells(rw, "C"), .Cells
(rw, "f"))

End With
With ws1
.Range(.Cells(SheetRow, "C"), .Cells
(SheetRow, "f")).Value = source.Value
End With

Next


End Sub


wrap the interior of the For..Next in an IF to stop
processing empty cells in column sheet2!B


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi,
I have in sheet 1, all the dates of the year 2003 in
column B.
In sheet 2, i import data from another workbook using a
macro.
The data from the other workbook contains dates, and
they are placed in the column B in sheet 2.
If i.e. field B7 in sheet 2 is 13.11.2003, i want to
write the values in cells c7,d7,e7,and f7 into the
c,d,e,and f cells next to my match of date in column
b,sheet1.

Can anyone please help me with this?

Jon-Henrik
.