comparing data and write values if true

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


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.
 
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.
 
Back
Top