Adapt following to your needs...

Hopefully you understand the referencing...
rng() is defined as an array of ranges.

rng(0) is the row for the report.
rng(1) is used for the first range in each sheet
rng(2) is used for the second.

rng(1)(irow,1) is a short notation for: rng(1).Cells(irow,1)


I've added hyperlinks for simple navigation.




Sub VarianceGenerator()
Const shName = "Index"
Const r1Addr = "B2:B57"
Const r2Addr = "D2:D57"

Dim wksRep As Worksheet
Dim rng(2) As Range
Dim iWks&, iRow&, iVar&

On Error Resume Next
Set wksRep = Worksheets(shName)
On Error GoTo 0
If wksRep Is Nothing Then
Set wksRep = Worksheets.Add(befo=1)
wksRep.Name = shName
End If

With wksRep
'Make sure the report is the first worksheet
If .Index < 1 Then .Move befo=1

.UsedRange.Clear
.Cells(1, 1) = "Variance Report"
.Cells(2, 1) = "'" & Now
Set rng(0) = .Cells(4, 1).Resize(1, 3)
rng(0) = Array("Hyperlink", "Value A", "Value B")
End With

'Test that the constants are set properly
Debug.Assert Range(r1Addr).Columns.Count = 1 And _
Range(r2Addr).Columns.Count = 1 And _
Range(r1Addr).Rows.Count = Range(r2Addr).Rows.Count

'Walk the sheets
For iWks = 2 To Worksheets.Count
Set rng(1) = Worksheets(iWks).Range(r1Addr)
Set rng(2) = Worksheets(iWks).Range(r2Addr)
'Walk the rows of the ranges
For iRow = 1 To rng(1).Rows.Count
If StrComp(rng(1)(iRow, 1), rng(2)(iRow, 1), vbTextCompare) Then
iVar = iVar + 1
With rng(0).Offset(iVar)
.Value = Array(, rng(1)(iRow, 1), rng(2)(iRow, 1))
.Cells(1, 1).Formula = _
"=HYPERLINK(""#" & Worksheets(iWks).Name & "!" & _
rng(1)(iRow, 1).Address(0, 0) & """)"
End With

End If
Next
Next

End Sub




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


koala wrote:


Is anyone able to assist with the following.

I have numerous spreadsheets in a workbook. (the amount always varies,
but usually between 40 and 50)

I need to check the contents of B2 against D2, B3 against D3 and so
down to B57 against D57. (for each of the sheets in the workbook). Note
Column B & D are “text”, and case does not matter, only content.

I then require a report (on another sheet called “variance report”)
that lists only those cells that differ:

When there is a difference, the report needs to show:

Sheet Name
Column A Value (Date)
Column B Value
Column D Value

I can post a small sample if needed.

Hoping someone is able to assist me with this assignment

Thanks in advance

Regards
Koala