First, you may find this workbook written by Myrna Larson and Bill Manville's
very informative:
http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla
====
Untested, but it did compile:
Option Explicit
Sub testme()
Dim myFolder As String
Dim CurWkbkName As String
Dim PrevWkbkName As String
Dim CurWkbk As Workbook
Dim PrevWkbk As Workbook
Dim cWks As Worksheet
Dim pWks As Worksheet
Dim myCell As Range
'I'm assuming that the Phase2 worksheet is in the workbook
'that owns the code.
With ThisWorkbook.Worksheets("phase2")
myFolder = .Range("d16").Value
If Right(myFolder, 1) <> "/" Then
myFolder = myFolder & "/"
End If
CurWkbkName = .Range("d17").Value
PrevWkbkName = .Range("D18").Value
End With
Set CurWkbk = Nothing
Set PrevWkbk = Nothing
On Error Resume Next
Set CurWkbk = Workbooks.Open(Filename:=myFolder & CurWkbkName)
Set PrevWkbk = Workbooks.Open(Filename:=myFolder & PrevWkbkName)
On Error Resume Next
If CurWkbk Is Nothing _
Or PrevWkbk Is Nothing Then
MsgBox "At least one -- maybe both -- workbooks weren't open"
Exit Sub
End If
For Each cWks In CurWkbk.Worksheets
Set pWks = Nothing
On Error Resume Next
Set pWks = PrevWkbk.Worksheets(cWks.Name)
On Error GoTo 0
If pWks Is Nothing Then
MsgBox cWks.Name & " wasn't found in: " & PrevWkbk.Name
Else
'this only checks the values in the used range
'of the current worksheet
'there could be more cells used in pWks
For Each myCell In cWks.UsedRange
If myCell.Value = pWks.Range(myCell.Address).Value Then
'it matched
Else
MsgBox myCell.Address & " didn't match"
End If
Next myCell
End If
Next cWks
End Sub
=======
Be aware that if there are additional sheets in the previous workbook (names
that don't match), then you're not finding them.
And if the current worksheet uses A1:B2 and the previous worksheet uses
A1:IV65536, then you're missing most of the sheet!
Matt wrote:
>
> I'm trying to write a piece of VBA that will check each cell in a workbook
> against each corresponding cell in another workbook, and highlight where
> there are differences.
>
> I'm really falling over at the first hurdle, as I cant get my head around
> how to reference the cells.
>
> I've got variables that tell me the workbook, the worksheet, the row and the
> column, but I don't appear able to so something as simple as check if
> wb1.ws1.cell1 = wb2.ws2.cell2.
>
> The code I have so far is below:
>
> Sub test()
>
> Dim wb1 As Workbook
> Dim wb1name As String
> Dim wb2 As Workbook
> Dim wb2name As String
> Dim ws1 As Worksheet
> Dim ws1name As String
> Dim ws2 As Worksheet
> Dim ws2name As String
> Dim cell1 As Range
> Dim cell2 As Range
> Dim cell1row As Long
> Dim cell1column As Long
> Dim cell2row As Long
> Dim cell2column As Long
> Dim filelocation As String
> Dim strCurrentworkbook As String
> Dim strpreviousworkbook As String
>
> filelocation = Workbooks("Position Check
> Model20100305.xls").Worksheets("phase2").Range("d" & 16)
> strCurrentworkbook = Workbooks("Position Check
> Model20100305.xls").Worksheets("phase2").Range("d" & 17)
> strpreviousworkbook = Workbooks("Position Check
> Model20100305.xls").Worksheets("phase2").Range("d" & 18)
>
> 'open currentworkbook
> ChDir filelocation
> Workbooks.Open Filename:=strCurrentworkbook
> 'open previousworkbook
> Workbooks.Open Filename:=strpreviousworkbook
>
> Set wb1 = Workbooks(strCurrentworkbook)
> Set wb2 = Workbooks(strpreviousworkbook)
>
> For Each ws1 In wb1.Worksheets
> ws1.Activate
> ws1name = ActiveSheet.Name
> ws2name = ws1name
>
> For Each cell1 In ws1.UsedRange
> cell1row = cell1.Row
> cell1column = cell1.Column
>
> MsgBox (ws1name & ", " & cell1row & ", " & cell1column)
>
> Next cell1
>
> Next ws1
>
> End Sub
--
Dave Peterson
Similar Threads
