You're right.. it's a simple task, with many
approaches.
But to the basics..
a)What version of Excel are you using?
Since you asked this question to a VBA group,
you must expect a VBA solution.
b) What do you know about VBA?
c) What do you have so far?
About the files:
d) What format is file 2 in?
Will the content from file1 be found ANYWHERE in file2
or will it be in a specific column?
e) Will the string from file1 be found as ENTIRE contents of a cell in File2?
(as in: "fox" is found in "The quick brown fox jumped over the lazy dog"
but if you're searching for the "entire contents", then it will NOT
return this as a match)
f) Will the search string be found more than once?
If so, how do you want to handle it?
Here's something I threw together, based on LOTS of assumptions
that I knew nothing about!
Option Explicit
Sub Search_for_Words()
Dim wbSource, shtSource
Dim wbData, shtData, tWord, wRows, inx
Dim dLastCell, dRows, dCols, R, C, DataRange As Range
Dim SrchWord, dRange As Range
'-------------------------------
wbSource = "file1.xlsb"
shtSource = "Criteria"
wbData = "file2.xlsb"
shtData = "Data"
Workbooks(wbSource).Activate
Sheets(shtSource).Select
Range("A1").Select
wRows = ActiveCell.SpecialCells(xlLastCell).Row
Workbooks(wbData).Activate
Sheets(shtData).Select
dLastCell = ActiveCell.SpecialCells(xlLastCell).Address
' dRows = ActiveCell.SpecialCells(xlLastCell).Row
' dCols = ActiveCell.SpecialCells(xlLastCell).Column
Set DataRange = Workbooks(wbData).Sheets(shtData).Range("A2:" & dLastCell)
Application.ScreenUpdating = False
For R = 2 To wRows
If (R Mod 10 = 0) Then Application.StatusBar = R & " of " & wRows & " =
" & Round(R / wRows * 100, 1) & "%"
SrchWord = Workbooks(wbSource).Sheets(shtSource).Cells(R, 1).Value
If (SrchWord & "X" <> "X") Then
With Worksheets(shtData).Range("A2:" & dLastCell)
Set dRange = .Find(What:=SrchWord, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If (Not dRange Is Nothing) Then
Workbooks(wbSource).Sheets(shtSource).Cells(R, 2).Value =
dRange.Address
End If
' Selection.FindNext(After:=ActiveCell).Activate
' Selection.Find(What:="abrasive", After:=ActiveCell,
LookIn:=xlFormulas, _
' LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
' MatchCase:=False, SearchFormat:=False).Activate
End With
Else
Exit For
End If
Next R
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "Finished"
End Sub
Paul
________________________________
From: Tom <tcli...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tue, February 1, 2011 1:47:07 AM
Subject: $$Excel-Macros$$ Read a cell contend of file1 and then find same in
file2.
I hope there is an expert who can help me to do this simple task:
Read the content of A1 (say ABC) in file1, and go and find (ABC not
XABC) in file2. I then do some work here.
Then go back and read the content of A2 in file1 and find it in file2.
I again do some work.
Repeat but stop if the cell content is a blank.
Thank you if you can help
Tom
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
