Hi Paul,
Thank you for your efforts.
I hope my answers will help to clarify what I am trying to do.
When I said, " I then do some work", I meant the program does some
work for me
but continues on without stopping.
On Feb 2, 12:33 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> You're right.. it's a simple task, with many
> approaches.
>
> But to the basics..
>
> a)What version of Excel are you using?
Currently Excel 2003 is my preference (Excel 2007 later because of its
increased columns).
>
> Since you asked this question to a VBA group,
> you must expect a VBA solution.
Correct.
>
> b) What do you know about VBA?
Self taught. Used to program in XLM. Long retired.
Trying to convert some old programs using VBA.
> c) What do you have so far?
Don't follow. Have?
Have done some financial modelling.
>
> About the files:
> d) What format is file 2 in?
Its txt but it is opened in Excel as a csv.
> Will the content from file1 be found ANYWHERE in file2
> or will it be in a specific column?
Only found in column A.
>
> e) Will the string from file1 be found as ENTIRE contents of a cell in File2?
If the string in file1 is ABC, the stings in file2 can be AABC, ABC,
ABCD not "A ABC" or "ABC D".
> (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?
No. There is only one exact match.
If it isn't found, return to file1 and read the next string down until
it is a blank and stop.
> 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!
Fair enough - a good effort.
I tried to run it under Excel 2003 but the compiler showed the
following steps in red.
Were they meant for Excel 2007?
>
> 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) & "%"
> <--- Red
> 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,
> <--- Red
> LookIn:=xlFormulas, _
> <--- Red
> LookAt:=xlPart, SearchOrder:=xlByRow
> <--- Red
> SearchDirection:=xlNext, _
> <--- Red
> MatchCase:=False, SearchFormat:=False)
> <--- Red
> If (Not dRange Is Nothing) Then
> Workbooks(wbSource).Sheets(shtSource).Cells(R, 2).Value =
> <--- Red
> dRange.Address
> End If
> ' Selection.FindNext(After:=ActiveCell).Activate
> ' Selection.Find(What:="abrasive", After:=ActiveCell,
> LookIn:=xlFormulas, _
> <--- Red
> ' LookAt:=xlWhole, SearchOrder:=xlByRows,
> <--- Red
> SearchDirection:=xlNext, _
> <--- Red
> ' MatchCase:=False, SearchFormat:=False).Activate
> <--- Red
> 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 athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://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
