Compare cell values in 2 different worksheets

[willcas]

Using Excel 97 I have 2 worksheets.
In worksheet1 I have sales data. In worksheet2 I have expected sales data. The structures of the Worksheets are the same. So I need to move from cell to cell in each worksheet and compare the values.

So if anyone has any code to do something similiar Id really appreciate a look at it.

Many thanks
Will

 

[MissyEd]

Hi there

Here is some code I wrote earlier that does something similar. I use named ranges rather than cell references, but you can amend it easily to use offsets or something simiar depending on the structure of your worksheets.

Public Sub sCommitBackPage2()
'
' Commits the Back Page 2 data to the Back Page
'

' 1. Create
Dim backWKSheet As Worksheet, back2WkSheet As Worksheet
Dim iDayCount As Integer, iArrCount As Integer
Dim arrNames(10) As String, strRange As String
Dim arrDay(7) As String

' 2. Initialise
Set backWKSheet = Worksheets("Back Page&quot
Set back2WkSheet = Worksheets("Back Page 2&quot

' 2.a Populate arrays
arrDay(0) = "Mon"
arrDay(1) = "Tue"
arrDay(2) = "Wed"
arrDay(3) = "Thu"
arrDay(4) = "Fri"
arrDay(5) = "Sat"
arrDay(6) = "Sun"

arrNames(0) = "PAIDFORMS_"
arrNames(1) = "PAIDITEMS_"
arrNames(2) = "NOTPAIDFORMS_"
arrNames(3) = "NOTPAIDITEMS_"
arrNames(4) = "PILLSFORMS_"
arrNames(5) = "PILLSITEMS_"
arrNames(6) = "NOCHARGEITEMS_"
arrNames(7) = "OWINGSLIPS_"
arrNames(8) = "NOREFUNDS_"
arrNames(9) = "NOVOIDS_"

' 3. Unprotect worksheets
backWKSheet.Unprotect "j1gglypuff2000!"
back2WkSheet.Unprotect "j1gglypuff2000!"

' 4. Loop to copy totals over
For iDayCount = 0 To 6
For iArrCount = 0 To 9
strRange = arrNames(iArrCount) & arrDay(iDayCount)
backWKSheet.Range(strRange).Value = _
back2WkSheet.Range(strRange).Value
Next iArrCount
Next iDayCount

' 5. Protect worksheets
backWKSheet.Protect password:="j1gglypuff2000!"
back2WkSheet.Protect password:="j1gglypuff2000!"

' 6. Destroy
Set backWKSheet = Nothing
Set back2WkSheet = Nothing
Erase arrDay, arrNames
strRange = ""
End Sub
Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com

 

[Francis]

Do you really need any code?
You could create a third worksheet and make each of the relevant cells the difference between the cell on the first sheet and the cell on the second sheet.