Need help to simplify VBA

S

smpatty08

Board Regular
Joined
May 16, 2014
Messages
155
I have this macro that works perfectly but I feel like I might be able to use loops and that would allow me to alter this slightly for other applications.

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

I would really appreciate some suggestions on how to make this more simple.

Thanks
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
You are on the rite track this will collapse nicely for you into loops but you start having issues when your warning msg no longer follows the pattern. you could use a third variable or just use a new loop. If you copied all the cell names that you want to make a message about into an array and just cycled through them one by one that would be your best bet. I started this out but without the WB to look at and test it every once and a while its hard for me to know I didn't get off track on the 43rd line by one and its all for not.



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 MyArray(0 To 4) As String ''''''''''''Increase as you add loops to put data in here'''''''''''
Dim MSGString1 As String, MSGString2 As String '''''''Stop some of the late binding you had '''''''''''''''

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

    MSGString1 = "Cell " ''''''''Putting these in strings will help VBA out a little
    MSGString2 = " does not match!"
    '''''''''This Loop needs to be copied over for each jump you have just start where you left off
    For i = 0 To 4
        MyArray(i) = "C" & i + 12
    Next i
    '''''''Like This'''''''''
'    For i = 5 To 14
'        MyArray(i) = "C" & i + 15
'    Next i
    For i = 0 To 4 '''''''''''''Then the 4 goes up as long as the pattern continues''''''''
        If Not y.Sheets("Sheet1").Cells((i * 3) + 1, 6) = Wksht1.Cells(i + 1, 1).Value Then
    
        MsgBox (MSGString1 & (i + MyArray(i)) & MSGString2)
    Next i
'If y.Sheets("Sheet1").Range("F2").Value = Wksht1.Range("A1").Value Then'''''And these go away''''''
'
'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

Hope that helps it can be a battle when it goes on for this long good luck
 
Upvote 0
You could indeed greatly simplify the code. For example, all of your If - Else - End If structures like:
Code: 
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
could be expressed as:
Code: 
    With y.Sheets("Sheet1")
      If .Range("F2").Value <> Wksht1.Range("A1").Value Then MsgBox ("Cell C12 does not match!")
      If .Range("F5").Value <> Wksht1.Range("A2").Value Then MsgBox ("Cell C13 does not match!")
      If .Range("F8").Value <> Wksht1.Range("A3").Value Then MsgBox ("Cell C14 does not match!")
      '.... etc.
    End With
If you did that with just the structures down to your 'Start T60' line, you'd reduce the code for that from 286 lines to just 58.

Even simpler, those 58 lines could be reduced to just 13 - better than a 95% reduction:
Code: 
    Dim i As Long, StrOut As String
    With y.Sheets("Sheet1")
      For i = 1 To 56
        If .Range("F" & i * 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
Plus you'd get just a single message box at the end instead of one for each & every error line.

The remainder of your code could likewise be streamlined.
 
Last edited:
Upvote 0
Macropod: Your code is exactly what I am looking for, although the code runs without errors, it tells me that every cell does not match. Here is how I implemented your 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
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

I do not know enough about this to know why it would tell me that all the cells do not match. I really appreciate your help.
 
Upvote 0
Hi smpatty,

Your code has:
If .Range("F" & 1 * 3 + 1).Value <> Wksht1.Range("A" & i).Value Then
where mine has:
If .Range("F" & i * 3 + 1).Value <> Wksht1.Range("A" & i).Value Then

Likewise with:
If .Range("N" & 1 * 3 + 1).Value <> Wksht1.Range("B" & z).Value Then

FWIW, you didn't need to use different variables for the T60 tests. You could have used:
Code: 
    Dim i As Long, StrOut As String
    With y.Sheets("Sheet1")
      For i = 1 To 56
        If .Range("F" & i * 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
      If StrOut <> "" Then MsgBox "The following cells do not match:" & vbCr & StrOut, vbExclamation
      StrOut = ""
      For i = 1 To 56
        If .Range("N" & i * 3 + 1).Value <> Wksht1.Range("B" & i).Value Then
          If i < 6 Then
            StrOut = StrOut & "D" & i + 11 & vbTab
          Else
            StrOut = StrOut & "D" & i + 14 & vbTab
          End If
        End If
      Next
      If StrOut <> "" Then MsgBox "The following cells do not match:" & vbCr & StrOut, vbExclamation
    End With
Alternatively, you could have used just one more variable and a single loop:
 
Upvote 0
Thank You for the help!!! I fixed the i where I had a 1, but I still couldn't get the code to work. But upon looking I noticed a small error. Where you had:
Code: 
        If .Range("F" & i * 3 [COLOR=#ff0000]+[/COLOR] 1).Value <> Wksht1.Range("A" & i).Value Then

I needed

Code: 
        If .Range("F" & i * 3 [COLOR=#FF0000]-[/COLOR] 1).Value <> Wksht1.Range("A" & i).Value Then

Because the first value is y.Sheets("Sheet1") is in cell "F2" not "F4" where your version was looking and that is why it kept telling me every cell didn't match.

Thank you very much for all of your help. I wish I would have been able to see that more quickly.
 
Upvote 0
Maybe I'm delusional but maybe:

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, x As Workbook, y As Workbook, Response As String
                    Dim m As Long, n As Long, Msg 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 WorkRange1 = Wksht2.Range("C12:C50"): Set WorkRange2 = Wksht2.Range("D12:D50")
Set y = Workbooks.Open("H:\Raw Data.xls")
    
    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."
    
    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
    
For n = 1 To 27: Msg = "Cell C" & 11 + n + m & "does not match!"
If y.Sheets("Sheet1").Range("F" & 3 * n - 1).Value <> Wksht1.Range("A" & n).Value Then MsgBox Msg
If n >= 5 Then m = 3
Next n

For n = 28 To 56: Msg = "Cell C" & 11 + n + m & "does not match!"
If y.Sheets("Sheet1").Range("F" & 3 * n - 4).Value <> Wksht1.Range("A" & n).Value Then MsgBox Msg
Next n: m = 0

For n = 1 To 56: Msg = "Cell D" & 11 + n + m & "does not match!"
If y.Sheets("Sheet1").Range("N" & 3 * n - 1).Value <> Wksht1.Range("B" & n).Value Then MsgBox Msg
If n >= 5 Then m = 3
Next n

y.Close (False)
Wksht1.Delete
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
There isn't enough memory to complete this action
Replies
4
Views
1K
alantse2010
A
E
  • Question
Userform with VBA
Replies
1
Views
116
Evie76
E
M
  • Solved
VBA comparing cell values dosen't work consistantly
Replies
6
Views
102
michaelehoudek
M
A
  • Question
VBA Run-time error '52' : Bad file name or number - save file on Sharepoint
Replies
7
Views
6K
alantse2010
A
G
  • Solved
Print resultValue from VBA script
Replies
5
Views
416
georgeeye
G

Forum statistics

Threads
1,214,606
Messages
6,120,490
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top