I am trying to write code that will match cell a1 in workbook A with ANY matching cell in workbook B. If it finds a matching cell in workbook B, then the macro must copy a range of cells (offset from the cell in workbook B) to workbook A. The macro must paste the range in a range offset from cell a1.
The process must repeat from a2 in workbook A. The process must stop when there are NO MORE CELLS TO MATCH AGAINST in workbook B.
This example finds a string in a RichTextBox control based on a word entered in a TextBox control. After it finds the specified string, it displays a message box that shows the number of the line containing the specified word. To try this example, put a RichTextBox control, a CommandButton control and a TextBox control on a form. Load a file into the RichTextBox, and paste this code into the General Declarations section of the form. Then run the example, enter a word in the TextBox, and click the CommandButton.
You may be better of in the Office Development (VBA) Forum.
In any case, there are plenty of examples posted on this site relating to your request. - Just do a search.
Requires work, but much faster than iterating each and every cell.
You will need to return the found (selected) cell once found. In this example I was looking for the Value in
Cell A1 of Sheet1, in Sheet2
the only problem with this is i will have to specify the number of iterations by copying the code and change a1 to a2, then copying and changing it again to a3, etc. do you agree?
one minor thing before i answer your questions: how do i modify the code to match cells between two Excel files (i.e. two workBOOKS)?
Answers to Questions:
1. when i find a matching value in WS2, it needs to offset 0 rows and 8 columns to the right. then i need to copy a range of 3 cells to the right. then i need to go back to WS1, find the cell which i JUST matched, then offset 8 cells to the right and paste.
2. yes the next step for me is to go onto a2, a3, etc. my problem is that i will never know how far i will need to go. it will be variable. it must work until there are no more cells in WS1 to match against WS2.
Turn the Sub Find_Match into a Function. Iterate through the WS1 "A" columb, passing the "A" X value (A1, A2, A3 etc until last used row) to the Function.
The copy pasting etc you can do as you know what you want to do with the data.
Yes, you can use 2 WorkBOOKS. Just create an instance of each, and use thier objects
instead of the static ones I used to test this.
Good luck.... (I'll be looking for your results tomorrow )
By default, lngIdx will be 0 when the function is called. (will also be 0 each and every time the funcion is called). However, I guess I should have assigned it 0 at the start of
the Function - just to be sure . If was global (public) then that would have been another issue, as it would retain the last value. Note, (as mentioned) if it has scope only
to that Function (or Sub) then it will be reset each time.
Now, the looping issue? Worked fine for me.....
Post the code YOU have at the moment please
this is the code i have. the data im testing it on compares sheet2, A1:A4 with sheet1, A1:A4.
the code keeps pasting the X in B10 and B11.
Public Function Find_Match()
Dim lngIdx As Long
'Start at A1 - IMPORTANT!
Worksheets("Sheet2").Range("A1").Select
If Worksheets("Sheet1").Range("a1").Value = Selection Then
'A1 WS2 is a match, do whatever. Now move on and check the rest
'Stuff
Worksheets("Sheet2").Range("b10").Value = "X"
Debug.Print ActiveCell.Row & " " & ActiveCell.Column '<<<<< For testing Only
End If
If ActiveCell.Row < lngIdx Then
Exit Do
Else
lngIdx = ActiveCell.Row
'Stuff here
Worksheets("Sheet2").Range("b11").Value = "X"
'
Debug.Print ActiveCell.Row & " " & ActiveCell.Column '<<<<< For testing Only
End If
At the bottom (where you have been replying to) select "Go Advanced"
When that page loads, you will see a "Manage Attachments" button - use that.
Should be self explanitory.
i think the problem is that due to my misunderstanding (our poor communication), i was trying to use your code to compare two columns of data. i.e. i thought the code would take cell a1, look for it in another sheet, find it, and then perform some action. Then take cell a2, look for in another sheet, find it, and then perform some action.... etc.
First part is correct. It WILL find ALL matches on Sheet2. But the other passes (for A2, A3 etc) have yet to be implimented. But that is why I added a parameter to the Sub, so we can do that latter.
Please dont take this the wrong way, it not ment to be an insult; but you seem out of your depth for this type of project - You need to be able to walk befor you run.
Reply With Quote
Originally Posted by malley
