checking variances in cells

[koala]

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 s
down to B57 against D57. (for each of the sheets in the workbook). Not
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
Koal

 

[Tom Ogilvy]

Sub GenReport()
Dim sh As Worksheet, sh1 As Worksheet
Dim bFirst As Boolean
Dim rw As Long
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Variance Report"
Set sh1 = ActiveSheet
rw = 1
For Each sh In Worksheets
If sh.Name <> sh1.Name Then
bFirst = True
For Each cell In sh.Range("B2:B57")
If LCase(cell.Value) <> LCase(cell.Offset(0, 2).Value) Then
If bFirst Then
sh1.Cells(rw, 1).Value = sh.Name
bFirst = False
rw = rw + 1
End If
cell.Offset(0, -1).Range("A1:B1,D1").Copy sh1.Cells(rw, 1)
rw = rw + 1
End If
Next
End If
Next
End Sub

 

[Guest]

Here is a Sub that I think will do it (modify as needed)...

Sub Variances()

Dim CheckSheet As Worksheet, CheckRow As Integer
Dim RowCounter As Integer

RowCounter = 1

For Each CheckSheet In ThisWorkbook.Worksheets

With CheckSheet

If .Name <> "variance report" Then

For CheckRow = 2 To 57

If UCase(.Range("B" & CheckRow).Value) <> UCase
(.Range("D" & CheckRow).Value) Then

Sheets("variance report").Range("A" &
RowCounter) = .Name
Sheets("variance report").Range("A" &
RowCounter + 1) = .Range("A" & CheckRow).Value
Sheets("variance report").Range("A" &
RowCounter + 2) = .Range("B" & CheckRow).Value
Sheets("variance report").Range("A" &
RowCounter + 3) = .Range("D" & CheckRow).Value
RowCounter = RowCounter + 4

End If

Next CheckRow

End If

End With

Next CheckSheet

End Sub

 

[keepITcool]

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 = "D257"

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(before:=1)
wksRep.Name = shName
End If

With wksRep
'Make sure the report is the first worksheet
If .Index <> 1 Then .Move before:=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 >