Thank you! This worked perfectly.
"Dave Peterson" wrote:
How about this--but both workbooks need to be open:
Option Explicit
Sub testme()
Dim myCell As Range
Dim myListRng As Range
Dim myLookThroughRng As Range
Dim FoundCell As Range
With Workbooks("book1.xls").Worksheets("sheet1")
Set myListRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With
With Workbooks("book2.xls").Worksheets("sheet99")
Set myLookThroughRng = .Range("a:a")
End With
For Each myCell In myListRng.Cells
With myLookThroughRng
Set FoundCell = .Cells.Find(what:=myCell.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False, searchdirection:=xlNext)
End With
If FoundCell Is Nothing Then
'not found
MsgBox myCell.Value & " wasn't found in: " _
& myLookThroughRng.Address(external:=True)
Else
FoundCell.Offset(0, 3).Value = "Y"
End If
Next myCell
End Sub
You'll have to adjust the workbook names and worksheet names to match your
stuff.
hnyb1 wrote:
I have several macros written to update information in a spreadsheet based on
an entry into a input box. I need to change these macros to instead look at
a column of numbers in another file and find each number in my spreadsheet
and update. The following is an example of one of the macros:
Dim equipNum As String
Do
equipNum = InputBox("Scan in the Equipment Number. Type Done to Exit")
Cells.Find(What:=equipNum, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Y"
Loop Until equipNum = "Done"
Range("A1").Select
I am using Excel 2003 and only have a moderate grasp on macros. Any help
would be greatly appreciated!
Thanks,
Holly
--
Dave Peterson
|