Find and Update Macro

H

hnyb1

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
 
D

Dave Peterson

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

hnyb1

Thank you! This worked perfectly.

Dave Peterson said:
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.
Click to expand...
 
Top