| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to copy value from 2nd file, if a word found in a correspondingrow of both files
I have two excel files.
One file has word William in Col Z A B C D E F G H I J skipped.. Z 123 34A B34 C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 34c c34 g54 g34 j322 m2 b3 f5 6 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 34e e34 i54 i34 l322 o2 d3 h5 6 William File continues....... till 500 rows 2nd file has word William in all values of Col Z A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 222 James Lee b34 r54 f34 321 3 4 6 7 William 321 Shawn Lee g54 t34 322 2 3 5 6 William 421 Jason Lee e54 f34 1322 3 4 6 9 William File continues....... till 500 rows I want to replace (B to J col) values of file 1 with (B to J col) of file 2 if COL Z matches William. It just copies the next row (COL b to COLJ VALUES with william in col Z) of from file 2. Final File A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 James Lee b34 r54 f34 321 3 4 6 7 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 Shawn Lee g54 t34 322 2 3 5 6 William File continues....... till 500 rows Any macros will be appreciated thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to copy value from 2nd file, if a word found in a correspond
This code may work. I think I undersant what you want but am not 100% sure.
Change the first two lines as required to make the workbook names and sheet agree with your spreadsheet. Sub ReplaceColumnns() Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") For RowCount = 1 To 500 With BK1Sht If .Range("Z" & RowCount) = "William" Then BK2Sht.Range("B" & RowCount & ":J" & RowCount).Copy _ destination:=.Range ("B" & RowCount) End If End With Next RowCount End Sub " wrote: I have two excel files. One file has word William in Col Z A B C D E F G H I J skipped.. Z 123 34A B34 C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 34c c34 g54 g34 j322 m2 b3 f5 6 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 34e e34 i54 i34 l322 o2 d3 h5 6 William File continues....... till 500 rows 2nd file has word William in all values of Col Z A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 222 James Lee b34 r54 f34 321 3 4 6 7 William 321 Shawn Lee g54 t34 322 2 3 5 6 William 421 Jason Lee e54 f34 1322 3 4 6 9 William File continues....... till 500 rows I want to replace (B to J col) values of file 1 with (B to J col) of file 2 if COL Z matches William. It just copies the next row (COL b to COLJ VALUES with william in col Z) of from file 2. Final File A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 James Lee b34 r54 f34 321 3 4 6 7 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 Shawn Lee g54 t34 322 2 3 5 6 William File continues....... till 500 rows Any macros will be appreciated thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to copy value from 2nd file, if a word found in acorrespond
Thanks alot.
I really appreciate your help. It is almost done. It is working excellent except one problem. File A contains "William" in some values of Col AF. But File B contains "William" in all values of Col AF It is copying data from exact row number of file b from Col AF. But i want to copy data from first ROW 1 (file b), THEN 2(file b) , THEN 3(file b), AND SO ON(file b becuase it contains "william" in all line)). For example, if FILE A is A..B....C.....AF a..1...2......William b..3...4...... c..5...6...... d..7...8......William e..9...10......William File b A..B....C.....AF q..11...12......William w..13...14......William e..15...16......William r..17...18......William s..19...20......William Result is a..11...12......William b..3...4...... c..5...6...... d..13...14......William e..15...16......William On Aug 30, 4:30*pm, Joel wrote: This code may work. I think I undersant what you want but am not 100% sure. Change the first two lines as required to make the workbook names and sheet agree with your spreadsheet. Sub ReplaceColumnns() Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") For RowCount = 1 To 500 * *With BK1Sht * * * If .Range("Z" & RowCount) = "William" Then * * * * *BK2Sht.Range("B" & RowCount & ":J" & RowCount).Copy _ * * * * * * destination:=.Range ("B" & RowCount) * * * End If * *End With Next RowCount End Sub " wrote: I have two excel files. One file has word William in Col Z A * * * * *B * *C * * * * D * E * * * * F * *G * H * * * *I * J skipped.. * * * * * * * * Z 123 * * *34A *B34 * C54 *D34 *323 * *2 * * 3 * * * 5 6 * * * * * * * * William 122 * * *34b *b34 * r54 * *f34 * 321 * *3 * * *a4 * *e6 *7 121 * * *34c *c34 * g54 * g34 *j322 * m2 * *b3 * *f5 *6 William 120 * * *34d *e34 * h54 * h34 *k321 * n3 * *c4 * *f6 *7 119 * * *34e *e34 * i54 * *i34 * l322 * o2 * *d3 * *h5 6 * * * * * * * *William File continues....... till 500 rows 2nd file has word William in all values of Col Z A * * * * *B * *C * * D * E * F * *G * H *skipped.. * * * * *Z 123 * * *Mike Lee *C54 *D34 *323 * 2 * *3 * *5 *6 * * * William 222 * * *James Lee *b34 * r54 *f34 *321 * 3 * *4 * *6 *7 William 321 * * *Shawn Lee * g54 *t34 *322 * 2 * *3 * *5 *6 * * * * * William 421 * * *Jason Lee * *e54 *f34 *1322 * 3 * *4 * *6 *9 William File continues....... till 500 rows I want to replace (B to J col) values of file 1 with (B to J col) of file 2 if COL Z matches William. It just copies the next row (COL b to COLJ VALUES with william in col Z) of *from file 2. Final File A * * * * *B * *C * * D * E * F * *G * H *skipped.. * * * * * * * * Z 123 * * *Mike Lee *C54 *D34 *323 * 2 * *3 * *5 *6 * * * William 122 * * *34b *b34 * r54 * *f34 * 321 * *3 * * *a4 * *e6 *7 121 * * *James Lee *b34 * r54 *f34 *321 * 3 * *4 * *6 *7 William 120 * * *34d *e34 * h54 * h34 *k321 * n3 * *c4 * *f6 *7 119 * * *Shawn Lee * g54 *t34 *322 * 2 * *3 * *5 *6 * * * * * William File continues....... till 500 rows Any macros will be appreciated thanks- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to copy value from 2nd file, if a word found in a corres
Sub ReplaceColumnns()
Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") BK2RowCount = 1 For BK1RowCount = 1 To 500 With BK1Sht If .Range("Z" & BK1RowCount) = "William" Then BK2Sht.Range("B" & BK2RowCount & ":J" & BK2RowCount).Copy _ destination:=.Range ("B" & BK1RowCount) BK2RowCount = BK2RowCount + 1 End If End With Next BK1RowCount End Sub " wrote: Thanks alot. I really appreciate your help. It is almost done. It is working excellent except one problem. File A contains "William" in some values of Col AF. But File B contains "William" in all values of Col AF It is copying data from exact row number of file b from Col AF. But i want to copy data from first ROW 1 (file b), THEN 2(file b) , THEN 3(file b), AND SO ON(file b becuase it contains "william" in all line)). For example, if FILE A is A..B....C.....AF a..1...2......William b..3...4...... c..5...6...... d..7...8......William e..9...10......William File b A..B....C.....AF q..11...12......William w..13...14......William e..15...16......William r..17...18......William s..19...20......William Result is a..11...12......William b..3...4...... c..5...6...... d..13...14......William e..15...16......William On Aug 30, 4:30 pm, Joel wrote: This code may work. I think I undersant what you want but am not 100% sure. Change the first two lines as required to make the workbook names and sheet agree with your spreadsheet. Sub ReplaceColumnns() Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") For RowCount = 1 To 500 With BK1Sht If .Range("Z" & RowCount) = "William" Then BK2Sht.Range("B" & RowCount & ":J" & RowCount).Copy _ destination:=.Range ("B" & RowCount) End If End With Next RowCount End Sub " wrote: I have two excel files. One file has word William in Col Z A B C D E F G H I J skipped.. Z 123 34A B34 C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 34c c34 g54 g34 j322 m2 b3 f5 6 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 34e e34 i54 i34 l322 o2 d3 h5 6 William File continues....... till 500 rows 2nd file has word William in all values of Col Z A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 222 James Lee b34 r54 f34 321 3 4 6 7 William 321 Shawn Lee g54 t34 322 2 3 5 6 William 421 Jason Lee e54 f34 1322 3 4 6 9 William File continues....... till 500 rows I want to replace (B to J col) values of file 1 with (B to J col) of file 2 if COL Z matches William. It just copies the next row (COL b to COLJ VALUES with william in col Z) of from file 2. Final File A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 James Lee b34 r54 f34 321 3 4 6 7 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 Shawn Lee g54 t34 322 2 3 5 6 William File continues....... till 500 rows Any macros will be appreciated thanks- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to copy value from 2nd file, if a word found in a corres
Excellent.
How can i delete the rows from file b wich are copied. thanks On Sep 2, 12:33*pm, Joel wrote: Sub ReplaceColumnns() Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") BK2RowCount = 1 For BK1RowCount = 1 To 500 * *With BK1Sht * * * If .Range("Z" & BK1RowCount) = "William" Then * * * * *BK2Sht.Range("B" & BK2RowCount & ":J" & BK2RowCount).Copy _ * * * * * * destination:=.Range ("B" & BK1RowCount) * * * * *BK2RowCount = BK2RowCount + 1 * * * End If * *End With Next BK1RowCount End Sub " wrote: Thanks alot. I really appreciate your help. It is almost done. It is working excellent except one problem. File A contains "William" in some values of Col AF. But File B contains "William" in all values of Col AF It is copying data from exact row number of file b *from Col AF. But i want to copy data from first ROW 1 (file b), THEN 2(file b) , THEN 3(file b), AND SO ON(file b becuase it contains "william" in all line)). For example, if FILE A *is A..B....C.....AF a..1...2......William b..3...4...... c..5...6...... d..7...8......William e..9...10......William File b A..B....C.....AF q..11...12......William w..13...14......William e..15...16......William r..17...18......William s..19...20......William Result is a..11...12......William b..3...4...... c..5...6...... d..13...14......William e..15...16......William On Aug 30, 4:30 pm, Joel wrote: This code may work. I think I undersant what you want but am not 100% sure. Change the first two lines as required to make the workbook names and sheet agree with your spreadsheet. Sub ReplaceColumnns() Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") For RowCount = 1 To 500 * *With BK1Sht * * * If .Range("Z" & RowCount) = "William" Then * * * * *BK2Sht.Range("B" & RowCount & ":J" & RowCount).Copy _ * * * * * * destination:=.Range ("B" & RowCount) * * * End If * *End With Next RowCount End Sub " wrote: I have two excel files. One file has word William in Col Z A * * * * *B * *C * * * * D * E * * * * F * *G * H * * * *I * J skipped.. * * * * * * * * Z 123 * * *34A *B34 * C54 *D34 *323 * *2 * * 3 * * * 5 6 * * * * * * * * William 122 * * *34b *b34 * r54 * *f34 * 321 * *3 * * *a4 * *e6 *7 121 * * *34c *c34 * g54 * g34 *j322 * m2 * *b3 * *f5 *6 William 120 * * *34d *e34 * h54 * h34 *k321 * n3 * *c4 * *f6 *7 119 * * *34e *e34 * i54 * *i34 * l322 * o2 * *d3 * *h5 6 * * * * * * * *William File continues....... till 500 rows 2nd file has word William in all values of Col Z A * * * * *B * *C * * D * E * F * *G * H *skipped.. * * * * *Z 123 * * *Mike Lee *C54 *D34 *323 * 2 * *3 * *5 *6 * * * William 222 * * *James Lee *b34 * r54 *f34 *321 * 3 * *4 * *6 *7 William 321 * * *Shawn Lee * g54 *t34 *322 * 2 * *3 * *5 *6 * * * * * William 421 * * *Jason Lee * *e54 *f34 *1322 * 3 * *4 * *6 *9 William File continues....... till 500 rows I want to replace (B to J col) values of file 1 with (B to J col) of file 2 if COL Z matches William. It just copies the next row (COL b to COLJ VALUES with william in col Z) of *from file 2. Final File A * * * * *B * *C * * D * E * F * *G * H *skipped.. * * * * * * * * Z 123 * * *Mike Lee *C54 *D34 *323 * 2 * *3 * *5 *6 * * * William 122 * * *34b *b34 * r54 * *f34 * 321 * *3 * * *a4 * *e6 *7 121 * * *James Lee *b34 * r54 *f34 *321 * 3 * *4 * *6 *7 William 120 * * *34d *e34 * h54 * h34 *k321 * n3 * *c4 * *f6 *7 119 * * *Shawn Lee * g54 *t34 *322 * 2 * *3 * *5 *6 * * * * * William File continues....... till 500 rows Any macros will be appreciated thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to copy value from 2nd file, if a word found in a corres
Sub ReplaceColumnns()
Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") BK2RowCount = 1 For BK1RowCount = 1 To 500 With BK1Sht If .Range("Z" & BK1RowCount) = "William" Then BK2Sht.Range("B" & BK2RowCount & ":J" & BK2RowCount).Copy _ destination:=.Range ("B" & BK1RowCount) BK2Sht.Rows(BK2RowCount).Delete End If End With Next BK1RowCount End Sub " wrote: Excellent. How can i delete the rows from file b wich are copied. thanks On Sep 2, 12:33 pm, Joel wrote: Sub ReplaceColumnns() Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") BK2RowCount = 1 For BK1RowCount = 1 To 500 With BK1Sht If .Range("Z" & BK1RowCount) = "William" Then BK2Sht.Range("B" & BK2RowCount & ":J" & BK2RowCount).Copy _ destination:=.Range ("B" & BK1RowCount) BK2RowCount = BK2RowCount + 1 End If End With Next BK1RowCount End Sub " wrote: Thanks alot. I really appreciate your help. It is almost done. It is working excellent except one problem. File A contains "William" in some values of Col AF. But File B contains "William" in all values of Col AF It is copying data from exact row number of file b from Col AF. But i want to copy data from first ROW 1 (file b), THEN 2(file b) , THEN 3(file b), AND SO ON(file b becuase it contains "william" in all line)). For example, if FILE A is A..B....C.....AF a..1...2......William b..3...4...... c..5...6...... d..7...8......William e..9...10......William File b A..B....C.....AF q..11...12......William w..13...14......William e..15...16......William r..17...18......William s..19...20......William Result is a..11...12......William b..3...4...... c..5...6...... d..13...14......William e..15...16......William On Aug 30, 4:30 pm, Joel wrote: This code may work. I think I undersant what you want but am not 100% sure. Change the first two lines as required to make the workbook names and sheet agree with your spreadsheet. Sub ReplaceColumnns() Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1") Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1") For RowCount = 1 To 500 With BK1Sht If .Range("Z" & RowCount) = "William" Then BK2Sht.Range("B" & RowCount & ":J" & RowCount).Copy _ destination:=.Range ("B" & RowCount) End If End With Next RowCount End Sub " wrote: I have two excel files. One file has word William in Col Z A B C D E F G H I J skipped.. Z 123 34A B34 C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 34c c34 g54 g34 j322 m2 b3 f5 6 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 34e e34 i54 i34 l322 o2 d3 h5 6 William File continues....... till 500 rows 2nd file has word William in all values of Col Z A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 222 James Lee b34 r54 f34 321 3 4 6 7 William 321 Shawn Lee g54 t34 322 2 3 5 6 William 421 Jason Lee e54 f34 1322 3 4 6 9 William File continues....... till 500 rows I want to replace (B to J col) values of file 1 with (B to J col) of file 2 if COL Z matches William. It just copies the next row (COL b to COLJ VALUES with william in col Z) of from file 2. Final File A B C D E F G H skipped.. Z 123 Mike Lee C54 D34 323 2 3 5 6 William 122 34b b34 r54 f34 321 3 a4 e6 7 121 James Lee b34 r54 f34 321 3 4 6 7 William 120 34d e34 h54 h34 k321 n3 c4 f6 7 119 Shawn Lee g54 t34 322 2 3 5 6 William File continues....... till 500 rows Any macros will be appreciated thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro - Open all word files in a directory | Excel Worksheet Functions | |||
| Macro to copy a word doc into an excel doc | Excel Discussion (Misc queries) | |||
| FOUND LOST FILE BUT COPY IS MISSING | Excel Worksheet Functions | |||
| Linking files "File Not Found" | Excel Discussion (Misc queries) | |||
| How to I create a pdf file from Word or Excel files | Excel Discussion (Misc queries) | |||
Linear Mode
