I have been trying to simplify this macro and with some help I have made some changes. The macro checks cells from 2 worksheets and makes sure they are the same, but now the macro says that all the cells are different when they are not.
Here is the original code:
here is the code that says all cells are different:
Can anybody help me figure out why this code is telling me that all the cells are different?
Here is the original code:
Code:
Sub Test()'To use this macro the Raw Data must be organized so that all the mean T0's are in column F and
'all the mean T60's are in column N
Dim WorkRange1 As Range, WorkRange2 As Range, WorkRange3 As Range, FoundCells As Range, Cell As Range
Dim Wksht1 As Worksheet, Wksht2 As Worksheet
Dim x As Workbook, y As Workbook
Response = MsgBox("Did you delete the Check Standard T0 and T60 from raw data?", vbYesNo)
If Response = vbNo Then Exit Sub
Set x = ActiveWorkbook
Sheets.Add After:=Sheets(Sheets.Count)
Set Wksht1 = ActiveSheet
Set Wksht2 = Sheets("HEMNA In Feed Calculations")
Set y = Workbooks.Open("H:\Raw Data.xls")
Set WorkRange1 = Wksht2.Range("C12:C50")
For Each Cell In WorkRange1
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "All cells are locked."
Else
End If
Set WorkRange2 = Wksht2.Range("D12:D50")
For Each Cell In WorkRange2
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "All cells are locked."
Else
FoundCells.Copy Destination:=Wksht1.Range("A1")
End If
If y.Sheets("Sheet1").Range("F2").Value = Wksht1.Range("A1").Value Then
Else
MsgBox ("Cell C12 does not match!")
End If
If y.Sheets("Sheet1").Range("F5").Value = Wksht1.Range("A2").Value Then
Else
MsgBox ("Cell C13 does not match!")
End If
If y.Sheets("Sheet1").Range("F8").Value = Wksht1.Range("A3").Value Then
Else
MsgBox ("Cell C14 does not match!")
End If
If y.Sheets("Sheet1").Range("F11").Value = Wksht1.Range("A4").Value Then
Else
MsgBox ("Cell C15 does not match!")
End If
If y.Sheets("Sheet1").Range("F14").Value = Wksht1.Range("A5").Value Then
Else
MsgBox ("Cell C16 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("F17").Value = Wksht1.Range("A6").Value Then
Else
MsgBox ("Cell C20 does not match!")
End If
If y.Sheets("Sheet1").Range("F20").Value = Wksht1.Range("A7").Value Then
Else
MsgBox ("Cell C21 does not match!")
End If
If y.Sheets("Sheet1").Range("F23").Value = Wksht1.Range("A8").Value Then
Else
MsgBox ("Cell C22 does not match!")
End If
If y.Sheets("Sheet1").Range("F26").Value = Wksht1.Range("A9").Value Then
Else
MsgBox ("Cell C23 does not match!")
End If
If y.Sheets("Sheet1").Range("F29").Value = Wksht1.Range("A10").Value Then
Else
MsgBox ("Cell C24 does not match!")
End If
If y.Sheets("Sheet1").Range("F32").Value = Wksht1.Range("A11").Value Then
Else
MsgBox ("Cell C25 does not match!")
End If
If y.Sheets("Sheet1").Range("F35").Value = Wksht1.Range("A12").Value Then
Else
MsgBox ("Cell C26 does not match!")
End If
If y.Sheets("Sheet1").Range("F38").Value = Wksht1.Range("A13").Value Then
Else
MsgBox ("Cell C27 does not match!")
End If
If y.Sheets("Sheet1").Range("F41").Value = Wksht1.Range("A14").Value Then
Else
MsgBox ("Cell C28 does not match!")
End If
If y.Sheets("Sheet1").Range("F44").Value = Wksht1.Range("A15").Value Then
Else
MsgBox ("Cell C29 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("F47").Value = Wksht1.Range("A16").Value Then
Else
MsgBox ("Cell C30 does not match!")
End If
If y.Sheets("Sheet1").Range("F50").Value = Wksht1.Range("A17").Value Then
Else
MsgBox ("Cell C31 does not match!")
End If
If y.Sheets("Sheet1").Range("F53").Value = Wksht1.Range("A18").Value Then
Else
MsgBox ("Cell C32 does not match!")
End If
If y.Sheets("Sheet1").Range("F56").Value = Wksht1.Range("A19").Value Then
Else
MsgBox ("Cell C33 does not match!")
End If
If y.Sheets("Sheet1").Range("F59").Value = Wksht1.Range("A20").Value Then
Else
MsgBox ("Cell C34 does not match!")
End If
If y.Sheets("Sheet1").Range("F62").Value = Wksht1.Range("A21").Value Then
Else
MsgBox ("Cell C35 does not match!")
End If
If y.Sheets("Sheet1").Range("F65").Value = Wksht1.Range("A22").Value Then
Else
MsgBox ("Cell C36 does not match!")
End If
If y.Sheets("Sheet1").Range("F68").Value = Wksht1.Range("A23").Value Then
Else
MsgBox ("Cell C37 does not match!")
End If
If y.Sheets("Sheet1").Range("F71").Value = Wksht1.Range("A24").Value Then
Else
MsgBox ("Cell C38 does not match!")
End If
If y.Sheets("Sheet1").Range("F74").Value = Wksht1.Range("A25").Value Then
Else
MsgBox ("Cell C39 does not match!")
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("F77").Value = Wksht1.Range("A26").Value Then
Else
MsgBox ("Cell C40 does not match!")
End If
If y.Sheets("Sheet1").Range("F80").Value = Wksht1.Range("A27").Value Then
Else
MsgBox ("Cell C41 does not match!")
End If
If y.Sheets("Sheet1").Range("F80").Value = Wksht1.Range("A28").Value Then
Else
MsgBox ("Cell C42 does not match!")
End If
If y.Sheets("Sheet1").Range("F83").Value = Wksht1.Range("A29").Value Then
Else
MsgBox ("Cell C43 does not match!")
End If
If y.Sheets("Sheet1").Range("F86").Value = Wksht1.Range("A30").Value Then
Else
MsgBox ("Cell C44 does not match!")
End If
If y.Sheets("Sheet1").Range("F89").Value = Wksht1.Range("A31").Value Then
Else
MsgBox ("Cell C45 does not match!")
End If
If y.Sheets("Sheet1").Range("F92").Value = Wksht1.Range("A32").Value Then
Else
MsgBox ("Cell C46 does not match!")
End If
If y.Sheets("Sheet1").Range("F95").Value = Wksht1.Range("A33").Value Then
Else
MsgBox ("Cell C47 does not match!")
End If
If y.Sheets("Sheet1").Range("F98").Value = Wksht1.Range("A34").Value Then
Else
MsgBox ("Cell C48 does not match!")
End If
If y.Sheets("Sheet1").Range("F101").Value = Wksht1.Range("A35").Value Then
Else
MsgBox ("Cell C49 does not match!")
End If
If y.Sheets("Sheet1").Range("F104").Value = Wksht1.Range("A36").Value Then
Else
MsgBox ("Cell C50 does not match!")
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("F107").Value = Wksht1.Range("A37").Value Then
Else
MsgBox ("Cell C51 does not match!")
End If
If y.Sheets("Sheet1").Range("F110").Value = Wksht1.Range("A38").Value Then
Else
MsgBox ("Cell C52 does not match!")
End If
If y.Sheets("Sheet1").Range("F113").Value = Wksht1.Range("A39").Value Then
Else
MsgBox ("Cell C53 does not match!")
End If
If y.Sheets("Sheet1").Range("F116").Value = Wksht1.Range("A40").Value Then
Else
MsgBox ("Cell C54 does not match!")
End If
If y.Sheets("Sheet1").Range("F119").Value = Wksht1.Range("A41").Value Then
Else
MsgBox ("Cell C55 does not match!")
End If
If y.Sheets("Sheet1").Range("F122").Value = Wksht1.Range("A42").Value Then
Else
MsgBox ("Cell C56 does not match!")
End If
If y.Sheets("Sheet1").Range("F125").Value = Wksht1.Range("A43").Value Then
Else
MsgBox ("Cell C57 does not match!")
End If
If y.Sheets("Sheet1").Range("F128").Value = Wksht1.Range("A44").Value Then
Else
MsgBox ("Cell C58 does not match!")
End If
If y.Sheets("Sheet1").Range("F131").Value = Wksht1.Range("A45").Value Then
Else
MsgBox ("Cell C59 does not match!")
End If
If y.Sheets("Sheet1").Range("F134").Value = Wksht1.Range("A46").Value Then
Else
MsgBox ("Cell C60 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("F137").Value = Wksht1.Range("A47").Value Then
Else
MsgBox ("Cell C61 does not match!")
End If
If y.Sheets("Sheet1").Range("F140").Value = Wksht1.Range("A48").Value Then
Else
MsgBox ("Cell C62 does not match!")
End If
If y.Sheets("Sheet1").Range("F143").Value = Wksht1.Range("A49").Value Then
Else
MsgBox ("Cell C63 does not match!")
End If
If y.Sheets("Sheet1").Range("F146").Value = Wksht1.Range("A50").Value Then
Else
MsgBox ("Cell C64 does not match!")
End If
If y.Sheets("Sheet1").Range("F149").Value = Wksht1.Range("A51").Value Then
Else
MsgBox ("Cell C65 does not match!")
End If
If y.Sheets("Sheet1").Range("F152").Value = Wksht1.Range("A52").Value Then
Else
MsgBox ("Cell C66 does not match!")
End If
If y.Sheets("Sheet1").Range("F155").Value = Wksht1.Range("A53").Value Then
Else
MsgBox ("Cell C67 does not match!")
End If
If y.Sheets("Sheet1").Range("F158").Value = Wksht1.Range("A54").Value Then
Else
MsgBox ("Cell C68 does not match!")
End If
If y.Sheets("Sheet1").Range("F161").Value = Wksht1.Range("A55").Value Then
Else
MsgBox ("Cell C69 does not match!")
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("F164").Value = Wksht1.Range("A56").Value Then
Else
MsgBox ("Cell C70 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Start T60
If y.Sheets("Sheet1").Range("N2").Value = Wksht1.Range("B1").Value Then
Else
MsgBox ("Cell D12 does not match!")
End If
If y.Sheets("Sheet1").Range("N5").Value = Wksht1.Range("B2").Value Then
Else
MsgBox ("Cell D13 does not match!")
End If
If y.Sheets("Sheet1").Range("N8").Value = Wksht1.Range("B3").Value Then
Else
MsgBox ("Cell D14 does not match!")
End If
If y.Sheets("Sheet1").Range("N11").Value = Wksht1.Range("B4").Value Then
Else
MsgBox ("Cell D15 does not match!")
End If
If y.Sheets("Sheet1").Range("N14").Value = Wksht1.Range("B5").Value Then
Else
MsgBox ("Cell D16 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("N17").Value = Wksht1.Range("B6").Value Then
Else
MsgBox ("Cell D20 does not match!")
End If
If y.Sheets("Sheet1").Range("N20").Value = Wksht1.Range("B7").Value Then
Else
MsgBox ("Cell D21 does not match!")
End If
If y.Sheets("Sheet1").Range("N23").Value = Wksht1.Range("B8").Value Then
Else
MsgBox ("Cell D22 does not match!")
End If
If y.Sheets("Sheet1").Range("N26").Value = Wksht1.Range("B9").Value Then
Else
MsgBox ("Cell D23 does not match!")
End If
If y.Sheets("Sheet1").Range("N29").Value = Wksht1.Range("B10").Value Then
Else
MsgBox ("Cell D24 does not match!")
End If
If y.Sheets("Sheet1").Range("N32").Value = Wksht1.Range("B11").Value Then
Else
MsgBox ("Cell D25 does not match!")
End If
If y.Sheets("Sheet1").Range("N35").Value = Wksht1.Range("B12").Value Then
Else
MsgBox ("Cell D26 does not match!")
End If
If y.Sheets("Sheet1").Range("N38").Value = Wksht1.Range("B13").Value Then
Else
MsgBox ("Cell D27 does not match!")
End If
If y.Sheets("Sheet1").Range("N41").Value = Wksht1.Range("B14").Value Then
Else
MsgBox ("Cell D28 does not match!")
End If
If y.Sheets("Sheet1").Range("N44").Value = Wksht1.Range("B15").Value Then
Else
MsgBox ("Cell D29 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("N47").Value = Wksht1.Range("B16").Value Then
Else
MsgBox ("Cell D30 does not match!")
End If
If y.Sheets("Sheet1").Range("N50").Value = Wksht1.Range("B17").Value Then
Else
MsgBox ("Cell D31 does not match!")
End If
If y.Sheets("Sheet1").Range("N53").Value = Wksht1.Range("B18").Value Then
Else
MsgBox ("Cell D32 does not match!")
End If
If y.Sheets("Sheet1").Range("N56").Value = Wksht1.Range("B19").Value Then
Else
MsgBox ("Cell D33 does not match!")
End If
If y.Sheets("Sheet1").Range("N59").Value = Wksht1.Range("B20").Value Then
Else
MsgBox ("Cell D34 does not match!")
End If
If y.Sheets("Sheet1").Range("N62").Value = Wksht1.Range("B21").Value Then
Else
MsgBox ("Cell D35 does not match!")
End If
If y.Sheets("Sheet1").Range("N65").Value = Wksht1.Range("B22").Value Then
Else
MsgBox ("Cell D36 does not match!")
End If
If y.Sheets("Sheet1").Range("N68").Value = Wksht1.Range("B23").Value Then
Else
MsgBox ("Cell D37 does not match!")
End If
If y.Sheets("Sheet1").Range("N71").Value = Wksht1.Range("B24").Value Then
Else
MsgBox ("Cell D38 does not match!")
End If
If y.Sheets("Sheet1").Range("N74").Value = Wksht1.Range("B25").Value Then
Else
MsgBox ("Cell D39 does not match!")
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("N77").Value = Wksht1.Range("B26").Value Then
Else
MsgBox ("Cell D40 does not match!")
End If
If y.Sheets("Sheet1").Range("N80").Value = Wksht1.Range("B27").Value Then
Else
MsgBox ("Cell D41 does not match!")
End If
If y.Sheets("Sheet1").Range("N83").Value = Wksht1.Range("B28").Value Then
Else
MsgBox ("Cell D42 does not match!")
End If
If y.Sheets("Sheet1").Range("N86").Value = Wksht1.Range("B29").Value Then
Else
MsgBox ("Cell D43 does not match!")
End If
If y.Sheets("Sheet1").Range("N89").Value = Wksht1.Range("B30").Value Then
Else
MsgBox ("Cell D44 does not match!")
End If
If y.Sheets("Sheet1").Range("N92").Value = Wksht1.Range("B31").Value Then
Else
MsgBox ("Cell D45 does not match!")
End If
If y.Sheets("Sheet1").Range("N95").Value = Wksht1.Range("B32").Value Then
Else
MsgBox ("Cell D46 does not match!")
End If
If y.Sheets("Sheet1").Range("N98").Value = Wksht1.Range("B33").Value Then
Else
MsgBox ("Cell D47 does not match!")
End If
If y.Sheets("Sheet1").Range("N101").Value = Wksht1.Range("B34").Value Then
Else
MsgBox ("Cell D48 does not match!")
End If
If y.Sheets("Sheet1").Range("N104").Value = Wksht1.Range("B35").Value Then
Else
MsgBox ("Cell D49 does not match!")
End If
If y.Sheets("Sheet1").Range("N107").Value = Wksht1.Range("B36").Value Then
Else
MsgBox ("Cell D50 does not match!")
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("N110").Value = Wksht1.Range("B37").Value Then
Else
MsgBox ("Cell D51 does not match!")
End If
If y.Sheets("Sheet1").Range("N113").Value = Wksht1.Range("B38").Value Then
Else
MsgBox ("Cell D52 does not match!")
End If
If y.Sheets("Sheet1").Range("N116").Value = Wksht1.Range("B39").Value Then
Else
MsgBox ("Cell D53 does not match!")
End If
If y.Sheets("Sheet1").Range("N119").Value = Wksht1.Range("B40").Value Then
Else
MsgBox ("Cell D54 does not match!")
End If
If y.Sheets("Sheet1").Range("N122").Value = Wksht1.Range("B41").Value Then
Else
MsgBox ("Cell D55 does not match!")
End If
If y.Sheets("Sheet1").Range("N125").Value = Wksht1.Range("B42").Value Then
Else
MsgBox ("Cell D56 does not match!")
End If
If y.Sheets("Sheet1").Range("N128").Value = Wksht1.Range("B43").Value Then
Else
MsgBox ("Cell D57 does not match!")
End If
If y.Sheets("Sheet1").Range("N131").Value = Wksht1.Range("B44").Value Then
Else
MsgBox ("Cell D58 does not match!")
End If
If y.Sheets("Sheet1").Range("N134").Value = Wksht1.Range("B45").Value Then
Else
MsgBox ("Cell D59 does not match!")
End If
If y.Sheets("Sheet1").Range("N137").Value = Wksht1.Range("B46").Value Then
Else
MsgBox ("Cell D60 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("N140").Value = Wksht1.Range("B47").Value Then
Else
MsgBox ("Cell D61 does not match!")
End If
If y.Sheets("Sheet1").Range("N143").Value = Wksht1.Range("B48").Value Then
Else
MsgBox ("Cell D62 does not match!")
End If
If y.Sheets("Sheet1").Range("N146").Value = Wksht1.Range("B49").Value Then
Else
MsgBox ("Cell D63 does not match!")
End If
If y.Sheets("Sheet1").Range("N149").Value = Wksht1.Range("B50").Value Then
Else
MsgBox ("Cell D64 does not match!")
End If
If y.Sheets("Sheet1").Range("N152").Value = Wksht1.Range("B51").Value Then
Else
MsgBox ("Cell D65 does not match!")
End If
If y.Sheets("Sheet1").Range("N155").Value = Wksht1.Range("B52").Value Then
Else
MsgBox ("Cell D66 does not match!")
End If
If y.Sheets("Sheet1").Range("N158").Value = Wksht1.Range("B53").Value Then
Else
MsgBox ("Cell D67 does not match!")
End If
If y.Sheets("Sheet1").Range("N161").Value = Wksht1.Range("B54").Value Then
Else
MsgBox ("Cell D68 does not match!")
End If
If y.Sheets("Sheet1").Range("N164").Value = Wksht1.Range("B55").Value Then
Else
MsgBox ("Cell D69 does not match!")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If y.Sheets("Sheet1").Range("N167").Value = Wksht1.Range("B56").Value Then
Else
MsgBox ("Cell D70 does not match!")
End If
y.Close (False)
Wksht1.Delete
End Sub
here is the code that says all cells are different:
Code:
Sub Test()
'To use this macro the Raw Data must be organized so that all the mean T0's are in column F and
'all the mean T60's are in column N
Dim WorkRange1 As Range, WorkRange2 As Range, WorkRange3 As Range, FoundCells As Range, Cell As Range
Dim Wksht1 As Worksheet, Wksht2 As Worksheet
Dim x As Workbook, y As Workbook
Dim i As Long, z As Long, StrOut As String, StrOut2 As String
Response = MsgBox("Did you delete the Check Standard T0 and T60 from raw data?", vbYesNo)
If Response = vbNo Then Exit Sub
Set x = ActiveWorkbook
Sheets.Add After:=Sheets(Sheets.Count)
Set Wksht1 = ActiveSheet
Set Wksht2 = Sheets("HEMNA In Feed Calculations")
Set y = Workbooks.Open("H:\Raw Data.xls")
Set WorkRange1 = Wksht2.Range("C12:C50")
For Each Cell In WorkRange1
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "All cells are locked."
Else
End If
Set WorkRange2 = Wksht2.Range("D12:D50")
For Each Cell In WorkRange2
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "All cells are locked."
Else
FoundCells.Copy Destination:=Wksht1.Range("A1")
End If
With y.Sheets("Sheet1")
For i = 1 To 56
If .Range("F" & 1 * 3 + 1).Value <> Wksht1.Range("A" & i).Value Then
If i < 6 Then
StrOut = StrOut & "C" & i + 11 & vbTab
Else
StrOut = StrOut & "C" & i + 14 & vbTab
End If
End If
Next
End With
If StrOut <> "" Then MsgBox "The following cells do not match:" & vbCr & StrOut, vbExclamation
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Start T60
With y.Sheets("Sheet1")
For z = 1 To 56
If .Range("N" & 1 * 3 + 1).Value <> Wksht1.Range("B" & z).Value Then
If z < 6 Then
StrOut2 = StrOut2 & "D" & z + 11 & vbTab
Else
StrOut2 = StrOut2 & "D" & z + 14 & vbTab
End If
End If
Next
End With
If StrOut2 <> "" Then MsgBox "The following cells do not match:" & vbCr & StrOut2, vbExclamation
y.Close (False)
Wksht1.Delete
End Sub
Can anybody help me figure out why this code is telling me that all the cells are different?