Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write a formula for a penalty for status of TRUE? | Excel Worksheet Functions | |||
Comparing Data Values and Scoring Similarities | Excel Discussion (Misc queries) | |||
Comparing data in two sheets with similar values | Excel Worksheet Functions | |||
Read lines from one worksheet, if value is true, write to new work | Excel Discussion (Misc queries) | |||
comparing two columns of data to find common values | Excel Discussion (Misc queries) |