Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a VB script for Excel
Well I think I need one.. It's when you are in trouble that you discovers forum like here. I hope someone can help and that I will also be able to help people in the future. BTW sorry for my english its not my mother tongue My problem: I have two files: file1.xls with A1 column numbered as below: 1 2 3 4 5 6 7 8 ... file2.xls with A1 column numbered as below: 1 2 3 7 8 15 28 ... Now what I need is a script that compares the two files and deletes the numbers from file1.xls that are not present in file2.xls (from the example above: 4,5,6,9,10,11,12,13,14,16...). So in the end file1.xls and file2.xls would have the same number of row. Please take note that file1.xls has a total of 8 columns and file2.xls 12 columns. File1.xls have around 2000 rows and file2.xls around 1200 rows. Anyone can help, please? Any other solution is welcome. I know how to use a VB script in Excel with one single file. Is it different when the script involves two files. Please give me some explanations just in case I have problems using it. Thanks in advance -- pretextat ------------------------------------------------------------------------ pretextat's Profile: http://www.excelforum.com/member.php...o&userid=36512 View this thread: http://www.excelforum.com/showthread...hreadid=562698 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a VB script for Excel
There probably are faster ways to do this byt the following should do what
you want. Sub Macro2() ' Dim retval Dim wk1, wk2 Set wk1 = Workbooks("Book1.xls") Set wk2 = Workbooks("Book12.xls") wk1.Activate Sheets("Sheet1").Activate Range("A1").Activate retval = ActiveCell wk2.Activate Sheets("Sheet1").Activate Range("A1").Activate For Each cell In Sheets Do If ActiveCell = "" Then wk1.Activate ActiveCell.Delete shift:=xlUp If ActiveCell = "" Then Exit Sub Else retval = ActiveCell wk2.Activate Range("A1").Activate End If ElseIf ActiveCell < retval Then ActiveCell.Offset(1, 0).Activate ElseIf ActiveCell = retval Then wk1.Activate ActiveCell.Offset(0, 1) = "OK" ' This line was used as a check ActiveCell.Offset(1, 0).Activate retval = ActiveCell wk2.Activate Range("A1").Activate End If Loop Next End Sub -- Best wishes, Jim "pretextat" wrote: Well I think I need one.. It's when you are in trouble that you discovers forum like here. I hope someone can help and that I will also be able to help people in the future. BTW sorry for my english its not my mother tongue My problem: I have two files: file1.xls with A1 column numbered as below: 1 2 3 4 5 6 7 8 ... file2.xls with A1 column numbered as below: 1 2 3 7 8 15 28 ... Now what I need is a script that compares the two files and deletes the numbers from file1.xls that are not present in file2.xls (from the example above: 4,5,6,9,10,11,12,13,14,16...). So in the end file1.xls and file2.xls would have the same number of row. Please take note that file1.xls has a total of 8 columns and file2.xls 12 columns. File1.xls have around 2000 rows and file2.xls around 1200 rows. Anyone can help, please? Any other solution is welcome. I know how to use a VB script in Excel with one single file. Is it different when the script involves two files. Please give me some explanations just in case I have problems using it. Thanks in advance -- pretextat ------------------------------------------------------------------------ pretextat's Profile: http://www.excelforum.com/member.php...o&userid=36512 View this thread: http://www.excelforum.com/showthread...hreadid=562698 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a VB script for Excel
One way is to use a helper column - in your first file enter in column B
=ISNUMBER(MATCH(A1, Sheet2!A$1:A$1200, 0)) and copy down. Then use autofilter (Data/Filter/Autofilter) and filter for the FALSE values in column B. Then delete the filtered values (or delete the entire rows). Turn off Autofilter. Delete the helper column w/the Match function. If you just deleted the unwanted data (not the entire row) you'll have gaps in your data after you turn off the filter, so you will need to sort your data to eliminate the gaps (Data/Sort). Be sure to backup before trying. "pretextat" wrote: Well I think I need one.. It's when you are in trouble that you discovers forum like here. I hope someone can help and that I will also be able to help people in the future. BTW sorry for my english its not my mother tongue My problem: I have two files: file1.xls with A1 column numbered as below: 1 2 3 4 5 6 7 8 ... file2.xls with A1 column numbered as below: 1 2 3 7 8 15 28 ... Now what I need is a script that compares the two files and deletes the numbers from file1.xls that are not present in file2.xls (from the example above: 4,5,6,9,10,11,12,13,14,16...). So in the end file1.xls and file2.xls would have the same number of row. Please take note that file1.xls has a total of 8 columns and file2.xls 12 columns. File1.xls have around 2000 rows and file2.xls around 1200 rows. Anyone can help, please? Any other solution is welcome. I know how to use a VB script in Excel with one single file. Is it different when the script involves two files. Please give me some explanations just in case I have problems using it. Thanks in advance -- pretextat ------------------------------------------------------------------------ pretextat's Profile: http://www.excelforum.com/member.php...o&userid=36512 View this thread: http://www.excelforum.com/showthread...hreadid=562698 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a VB script for Excel
well thank you both for your help, both it didnt work out Jim Jackson the script work but in the end the new sheet didnt produce what I expected. jmb the formula dont do exaclt whay I want In both case its because I was not clear enough in my explanation. I will detailed my example it will be probable easier with same file in different sheets _*Sheet1:*_ (column A is the number, B the fruit, C the name) row1: 1 banana Patrick row2: 2 apple Steve row3: 3 pear Helena row4: 4 cherry Jim row5: 5 strawberry Joe row6: 6 rasperry Darryl row7: 7 banana Jim row8: 8 pear Patrick _*Sheet2:*_ (column A is the number, B sport, C color) row1: 1 baseball green row2: 2 hockey blue row3: 5 football red row4: 8 racing white What I want in sheet1 or whatever (something like a merge between sheet 1 and 2 but with the A column of sheet2): row1: 1 banana Patrick baseball green row2: 2 apple Steve hockey blue row3: 5 strawberry Joe football red row4: 8 pear Patrick racing white The problem with the formula above is that row1 and row2 came with "TRUE" but I got "FALSE" for row3, row4 and so on. I understand that I hope that my explanation are better than before now. -- pretextat ------------------------------------------------------------------------ pretextat's Profile: http://www.excelforum.com/member.php...o&userid=36512 View this thread: http://www.excelforum.com/showthread...hreadid=562698 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a VB script for Excel
JMB Wrote: One way is to use a helper column - in your first file enter in colum B =ISNUMBER(MATCH(A1, Sheet2!A$1:A$1200, 0)) and copy down. Then use autofilter (Data/Filter/Autofilter) and filter for the FALS values in column B. Then delete the filtered values (or delete the entir rows). Turn off Autofilter. Delete the helper column w/the Match function. If you just deleted the unwanted data (not the entire row) you'll have gaps i your data after you turn off the filter, so you will need to sort your dat to eliminate the gaps (Data/Sort). Be sure to backup before trying. [/color] The formula works fine, since I use the french version of Excel I didn cut and paste your formula but rewrite it and I forgot to put the $. realize that last night in my sleep! Thanks a lot I own you one JMB Jim Jackson Thanks to you too for your time and script -- pretexta ----------------------------------------------------------------------- pretextat's Profile: http://www.excelforum.com/member.php...fo&userid=3651 View this thread: http://www.excelforum.com/showthread.php?threadid=56269 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a VB script for Excel
I'm glad it worked out for you. Sorry for taking so long to get back, we had
a storm go through yesterday and lost power. "pretextat" wrote: JMB Wrote: One way is to use a helper column - in your first file enter in column B =ISNUMBER(MATCH(A1, Sheet2!A$1:A$1200, 0)) and copy down. Then use autofilter (Data/Filter/Autofilter) and filter for the FALSE values in column B. Then delete the filtered values (or delete the entire rows). Turn off Autofilter. Delete the helper column w/the Match function. If you just deleted the unwanted data (not the entire row) you'll have gaps in your data after you turn off the filter, so you will need to sort your data to eliminate the gaps (Data/Sort). Be sure to backup before trying. The formula works fine, since I use the french version of Excel I didnt cut and paste your formula but rewrite it and I forgot to put the $. I realize that last night in my sleep! Thanks a lot I own you one JMB Jim Jackson Thanks to you too for your time and script. -- pretextat ------------------------------------------------------------------------ pretextat's Profile: http://www.excelforum.com/member.php...o&userid=36512 View this thread: http://www.excelforum.com/showthread...hreadid=562698 [/color] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
Excel 2000/XP script to Excel97 script | Excel Programming | |||
VBA Script in Excel | Excel Programming |