Check value for each step 10 row

[sbv1986]

Hi all

I have sheets("data") and want macro to do that: Check data if wrong will copy to sheet("false"), check each 10 rows and again to last row have data (i = 3, i = 13, i = 23.. and continous)

x = 1 to last row in sheets("false")
With sheets("data")
For each row i = 3 to lasrow
For each columns j = 5 to 15
 if .cells(i,j).value <> Sum(.cells(i+1,j);.cells(i+10;j)) then Sheets("false").Range("Ax").value = .range("Ai") & " column"& j
 if .cells(i,j).value <> Sum(.cells(i,5);.cells(i,15)) then Sheets("false").Range("Ax").value = .range("Ai") & " row"& i
Next j
Next i = i +10

Please help me to do that with VBA code. Thanks./.

 

[sbv1986]

Some one can help me?

 

[Joe4]

Quite frankly, your question is not very clear to me.
It might be more helpful if you could post a sample of your data and walk us through an actual example.

Note that you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[sbv1986]

Thanks @Joe, this iss my explaining to clear my idea:
Sheets("data") look like this
With storeA have false value at:
F3 = Sum(F4:F12) = 774 but result is 775
H3 = sum(H4:H12) = 792 but result is 793
E4 = sum(F4:O4) = 2665 but result is 2666
continous check another store to the end
5 6 7 8 9 10 11 12 13 14 15
storeA 3 47025 775 783 793 801 7290 7299 7308 7317 7326 7335
storeA 4 2666 46 47 48 49 410 411 412 413 414 415
storeA 5 3305 56 57 58 59 510 511 512 513 514 515
storeA 6 3945 66 67 68 69 610 611 612 613 614 615
storeA 7 4585 76 77 78 79 710 711 712 713 714 715
storeA 8 5225 86 87 88 89 810 811 812 813 814 815
storeA 9 5865 96 97 98 99 910 911 912 913 914 915
storeA 10 6505 106 107 108 109 1010 1011 1012 1013 1014 1015
storeA 11 7145 116 117 118 119 1110 1111 1112 1113 1114 1115
storeA 12 7785 126 127 128 129 1210 1211 1212 1213 1214 1215
StoreB 13 104625 1674 1683 1692 1701 16290 16299 16308 16317 16326 16335
StoreB 14 9065 146 147 148 149 1410 1411 1412 1413 1414 1415
StoreB 15 9705 156 157 158 159 1510 1511 1512 1513 1514 1515
StoreB 16 10345 166 167 168 169 1610 1611 1612 1613 1614 1615
StoreB 17 10985 176 177 178 179 1710 1711 1712 1713 1714 1715
StoreB 18 11625 186 187 188 189 1810 1811 1812 1813 1814 1815
StoreB 19 12265 196 197 198 199 1910 1911 1912 1913 1914 1915
StoreB 20 12905 206 207 208 209 2010 2011 2012 2013 2014 2015
StoreB 21 13545 216 217 218 219 2110 2111 2112 2113 2114 2115
StoreB 22 14185 226 227 228 229 2210 2211 2212 2213 2214 2215

And I want after VBC check sheets("data"), mistake found will apper in sheets("false") look like:

storeA_column 6
storeA_column 8
storeA__row 4

Please help me.

 

[Joe4]

Try this:

Sub MyMacro()

    Dim i As Long, j As Long
    Dim lastRow As Long
    Dim rng As Range
    
'   Set initial value of i
    i = 3

    Application.ScreenUpdating = False

'   Find last row on data sheet
    lastRow = Sheets("data").Cells(Rows.Count, "F").End(xlUp).Row

    With Sheets("data")
'       Loop every 10 rows
        Do
'           Loop through columns 5 to 15
            For j = 5 To 15
'               If totals do not match, write to "false" sheet
                Set rng = Range(.Cells(i + 1, j), .Cells(i + 9, j))
                If .Cells(i, j).Value <> Application.Sum(rng) Then
                    Sheets("false").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = .Range("A" & i) & " column" & j
                End If
            Next j
'           Increment i by 10
            i = i + 10
'           Exit if no more data
            If i > lastRow Then Exit Do
        Loop
    End With

    Application.ScreenUpdating = True

    MsgBox "Finished"
    
End Sub

NOTE: On my version of Excel, the comma (,) is the separator for arguments. Based on your original post, it looks like the semi-colon( may be yours.
So you may need to separate the commas in myh code with semi-colons.

 

[sbv1986]

Many thanks @Joe4
Code work with check sum column but there's not work woth check sum row.
Mistake at row 4 and code not find them when E4 = sum(F4:O4) = 2665 but result is 2666

Please help me one time

 

[Joe4]

I knew you wanted the "check sum column", but I missed that you also wanted a "check sum row".
It works better if we do the row check first. Try this:

Sub MyMacro()

    Dim i As Long, j As Long, r As Long
    Dim lastRow As Long
    Dim rng As Range
    
'   Set initial value of i
    i = 3

    Application.ScreenUpdating = False

'   Find last row on data sheet
    lastRow = Sheets("data").Cells(Rows.Count, "F").End(xlUp).Row

    With Sheets("data")

'       Check row sums
        For r = i To lastRow
            Set rng = Range(.Cells(r, 6), .Cells(r, 15))
            If .Cells(r, 5).Value <> Application.Sum(rng) Then
                Sheets("false").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = .Range("A" & r) & " row" & r
            End If
        Next r

'       Check column sums
        Do
'           Loop through columns 5 to 15
            For j = 5 To 15
'               If totals do not match, write to "false" sheet
                Set rng = Range(.Cells(i + 1, j), .Cells(i + 9, j))
                If .Cells(i, j).Value <> Application.Sum(rng) Then
                    Sheets("false").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = .Range("A" & i) & " column" & j
                End If
            Next j
'           Increment i by 10
            i = i + 10
'           Exit if no more data
            If i > lastRow Then Exit Do
        Loop
   
    End With

    Application.ScreenUpdating = True

    MsgBox "Finished"
    
End Sub

 

[sbv1986]

Thanks code work fine./.

 

[Joe4]

You are welcome.