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

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

MsgBox "At least one -- maybe both -- workbooks weren't open"
should have said:
MsgBox "At least one -- maybe both -- workbooks weren't opened"

(it tries to open them--not check to see if they're open.)

Dave Peterson wrote:

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

--

Dave Peterson