Macro for Match Value in another workbook
 

Hello All,

Im trying to modify a macro I've used in the past for another routine, but
Im not having much luck and could use a little advice.

Im basically looking to perform a Match and Copy.Cell.Offset.Value to
another workbook. The first ActiveWorkbook is referenced as Oldbook. The
Oldbook Row 5 is the column Im looking to match against Workbook 2
(FileName) Row 1 contents. I would like the Macro to evaluate/match each
individual cell value in rows 1 through 200 (Oldbook). If there is a Match
in the FileName book, Id like to write Oldbook values for Row 7, 8, 10 to
rows 2, 3 and 4 in FileName book for that particular match (and continue down
the worksheet).

I know this request might be a bit confusing, but Im hoping this along with
the Macro might shed additional light on the basics of my request.

Thanks for your review and thoughts €“ Roger


Sub fyCompare()
Dim Msg As String
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String

On Error Resume Next
Application.ScreenUpdating = False
Msg = "Unable to find"
Path = "C:\Documents and Settings\Roger\Desktop\"
Filename = "Events.xls"

Oldbook = ActiveWorkbook.Name
Err = 0

If WorkbookIsOpen(Filename) = False Then
Workbooks.Open Filename:=Path & Filename
Else
Workbooks(Filename).Activate
End If
If Err < 0 Then
MsgBox Msg & Path & Filename, vbCritical, "Error"
Exit Sub
End If

Workbooks(Filename).Activate

For r = 1 To 200

'<<<<<< need macro that steps down and
'evaluates each match and writes to offset cells


End If
Next r

Workbooks(Filename).Close
Application.ScreenUpdating = True
End Sub
Private Function WorkbookIsOpen(wbName) As Boolean
' Returns TRUE if the workbook is open
Dim X As Workbook
On Error Resume Next
Set X = Workbooks(wbName)
If Err = 0 Then WorkbookIsOpen = True _
Else: WorkbookIsOpen = False
On Error GoTo 0
End Function