R
Roger
Hello All,
I’m trying to modify a macro I've used in the past for another routine, but
I’m not having much luck and could use a little advice.
I’m 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 I’m 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, I’d 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 I’m 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
I’m trying to modify a macro I've used in the past for another routine, but
I’m not having much luck and could use a little advice.
I’m 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 I’m 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, I’d 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 I’m 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