VBA to find Cell Range in Files in Folder, return value
Sub Quote()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim sTarget as String
Dim rng as Range
Dim sh as worksheet
set sh = Workbooks("June.xls").Worksheets(1)
sTarget = sh.Range("A1").Value
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Javier\My Documents\Test\"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Do While FNames < ""
Set mybook = Workbooks.Open(Filename:=FNames, _
UpdateLinks:=0)
set rng = myBook.Worksheets(1).Cells.Find(sTarget)
if not rng is nothing then
sh.Range("B1").Value = rng.Parent.Range("B25").Value
mybook.close SaveChanges:=False
exit do
end if
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
--
Regards,
Tom Ogilvy
"JavyD" wrote in message
...
Hey Tom, long time no see. LOL. No specific file, all .xls in a folder,
say
my docs. But this time, I want it to target a specific value. Say I want
the VBA to look at my file, say June. I want it to see A1 in June Sheet
1,
look at its value, look for that value in a folder in all xls, once it
finds
a work book with sheet 1 with that value, which always is in the same
range
if that helps, I want it to bring back a value that is in another range in
the found work book, and imported back to cell B1 in June
"Tom Ogilvy" wrote:
A specific workbook or are you talking about opening all workbooks and
looking for the value. If the latter, you should be able to modify the
code
you already have. Again, you have talked about a cell address in a
workbook, but haven't said what sheet to look on.
--
Regards,
Tom Ogilvy
"JavyD" wrote in message
...
Hello guys,
Is it possible to create a VBA for my existing file, look at the
Target in
say CEll A1, then look into work books in a folder, find that Target
in a
work book, and bring back a value that would be in cell B25? Is this
possible?
|