Printable View
Hi
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.
can anyone help?
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.
VB Code:
Hi malley, Welcome to the Forum :wave:
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.
(I will try to dig one up) :)
shakti5385, chalk and cheese my friend.
That example has nothing do with his query - sorry :)
thanks for your responses. if there are existing threads which solve my problem, do i do a search using the keyword "find"?
Have a look at post 6 of this tread: http://www.vbforums.com/showthread.p...ighlight=excel, it may give you some ideas (I'll post back if I can find sometning better)
The following may be a place to start:
VB Code:
This worked (in principle):
VB Code:
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?
No :)
It can be automated. I am playing with it now. I'm just trying to sort out the Find iteration.
Part A nearly finished. This may give you an Idea:
VB Code:
Question time for you.
What is this offset range value? Need this so we can go on.
The code above will ONLY capture a cell in WS2, that matches cell A1 of WS1.
The next step will be to go on to A2, A3 etc. But how far considering you want a range pasted into WS1 - that will screw with the original data no?
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.
Bugger, it's buggy; if cell A1 in WS2 was a match, this method wouldn't find it...... (After=ActiveCell since we have to focus on A1 at the start)
Option 2 above.
Cant you just add these values (matches from Sheet2) to a NEW Worksheet?
yes it definetly is a bugger! i much appreciate your help though.
What is the answer to post #14 above :)
I have a different approach (automating "Find All"), but will need time to refine it.
If I can't sus it out tonight, I'll post back tomorrow ;)
thanks for your help
Ok, A1 WS2 bug taken care of...
VB Code:
Where to from here.
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 ;) ;) )
thanks for this. unfortunately im having problems. so ill address the first item.
i changed the code to a private function. the macro then keeps looping. it doesnt seem to be able to exit the loop.
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
Do
DoEvents
Worksheets("Sheet2").Cells.Find(What:=Worksheets("Sheet1").Range("a1").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
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
Loop
End Function
Use VBCODE tags to post your code snippets (see my Sig)
I have reposted the above:
VB Code:
No it isnt! (A1 only)Quote:
Originally Posted by malley
Ok, slight change as your doing the work from within, it may aswell be a Sub (as opposed to a Function, which would normaly return).
1. I made it a SUB,
2. Included a Sub Parameter (rngRange) for future use,
3. I set lngIdx = 0.
VB Code:
sorry i had the wrong impression there. just to confirm, your code will take cell sheet2!A1 and search for it in sheet1.
Can you attach the WorkBook(s).
Close, will find all instances of WS1 "A1" value within WS2.Quote:
Originally Posted by malley
how do i attach files?
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.
ok here is the file.
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.
what do you mean "added a parameter"? what exactly did you add?
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.
Why are you doing this? Assignment? Work?
i am out of my depth! its for work. unfortunately i have no choice but to sort this out.
Are you a paid programmer?
no finance analyst. i have very little knowledge of VB (obviously) but in the past have been able to get by using these forums.
no. finance analyst. i have very little knowledge of VB (obviously) but in the past have been able to get by using these forums.
I am now concerned as without days of BETA testing with dummy data etc, this is fraught with danger! - Are you aware of that.
no i was not aware of that. for the moment though, i need to reach a temporary solution. i have to go lunch now!
Sometimes the cheapest solution isn't obvious. Have your company hire either a consultant to do this one project, or a programmer to do all your programming. In your field, writing a bad program can sometimes cost more than is apparent until the bad stuff hits the fan.Quote:
Originally Posted by malley
Moved.
Bruce, regarding this code you wrote for me, if i want to check cell A2, A3, A4, etc against a set of data (instead of just A1), would I have to copy the code 3 times and change the reference to A1 to the relevent cell in column A?
I just stumbled across this thread; hopefully, you don't mind my chiming in.
Regarding cells A2, A3, and A4: One thing that you could try is, once a match has been found for cell A1, to then compare cell A2 to the cell immediately below the matching cell AND compare cell A3 to the cell which is 2 cells below the original matching cell, etc.
That way you only have to run the whole search thing once (for cell A1) and then check the other 3 cells for matches whenever you have a match for cell A1.
For instance, if cell C43 matched cell A1, then compare A2 to C44, A3 to C45, and A4 to C46. If all 4 match then you have a successful "total match".