Find function - PLEASE HELP!

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:

Private Sub Command1_Click()
   Dim FoundPos As Integer
   Dim FoundLine As Integer
   ' Find the text specified in the TextBox control.
   FoundPos = RichTextBox1.Find(Text1.Text, , , rtfWholeWord)
 
   ' Show message based on whether the text was found or not.
 
   If FoundPos <> -1 Then
      ' Returns number of line containing found text.
      FoundLine = RichTextBox1.GetLineFromChar(FoundPos)
      MsgBox "Word found on line " & CStr(FoundLine)
   Else
      MsgBox "Word not found."
   End

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:

intRowCount = objExcel.ActiveSheet.UsedRange.Rows.Count 'Capture the number of USED Rows
    intColCount = objExcel.ActiveSheet.UsedRange.Columns.Count  'Capture the number of USED Columns
 
    For intRowIdx = 1 To intRowCount  'Iterate each Row
        For intColIdx = 1 To intColCount  'Iterate each Column
            If whatever = objExcel.ActiveSheet.Cells(intRowIdx, intColIdx).Value Then Msgbox whatever & " item found!", VbOkOnly + vbInformation, "Found"
        Next
    Next

This worked (in principle):

VB Code:

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

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:

Private Sub Find_Match()
Dim lngIdx As Long
 
    'Start at A1 - IMPORTANT!
    Worksheets("Sheet2").Range("A1").Select
 
    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
            '
            Debug.Print ActiveCell.Row & "  " & ActiveCell.Column '<<<<<< For Testing ONLY
 
        End If
 
    Loop
 
End Sub

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:

Private Sub 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
        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
            '
            '
            Debug.Print ActiveCell.Row & "  " & ActiveCell.Column   '<<<<< For testing Only
        End If
 
    Loop
 
End Sub

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:

Option Explicit
 
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

Quote:

---

Originally Posted by malley

this is the code i have. the data im testing it on compares sheet2, A1:A4 with sheet1, A1:A4.

---

No it isnt! (A1 only)

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:

Option Explicit
 
Public Sub Find_Match(rngRange As Range)
Dim lngIdx As Long
 
lngIdx = 0
 
'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 Sub

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).

Quote:

---

Originally Posted by malley

sorry i had the wrong impression there. just to confirm, your code will take cell sheet2!A1 and search for it in sheet1.

---

Close, will find all instances of WS1 "A1" value within WS2.

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!