comparing data and write values if true

J

Jon-Henrik

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
 
D

David McRitchie

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 said:
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
Click to expand...
my match of date in column b,sheet1.
 
P

Patrick Molloy

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
Click to expand...
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
Click to expand...
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.
 
Top