doubt on calculating difference vba/excel

[SANDRADEPAR]

Can someone give some advices???
Id like to know if someone have a code for visual basic/ excel to calculate the exact difference of two numbers in a extremely huge sample, such as.

34    46    61    70    73    78    88    89    90    95    100
49    51    55    62    63    64    76    82    84    93    97
47    48    54    55    57    67    74    79    85    91    99
49    51    55    60    63    64    67    78    83    93    98
54    59    65    69    71    73    87    92    98    99    100
42    47    48    58    72    73    74    75    76    80    93
50    56    59    64    70    73    85    92    94    97    99
53    57    59    65    74    75    83    84    94    99    100
30    32    36    60    67    69    70    73    77    98    100
51    55    60    63    65    79    81    86    87    95    98
54    58    64    71    74    78    79    83    87    93    94
37    39    43    49    52    56    63    65    81    88    89
59    66    68    75    76    79    82    84    88    91    100
40    43    52    55    59    76    81    83    85    86    97
29    31    38    41    48    54    65    73    92    94    97
46    48    49    51    57    59    63    73    78    91    94
39    43    47    59    61    78    79    92    96    97    98
69    70    76    78    79    85    91    92    96    97    100
36    48    53    67    77    82    86    94    98    99    100
60    61    65    70    71    72    75    84    90    93    97
54    59    67    71    73    75    77    89    92    97    99
42    45    49    56    79    84    85    93    94    95    100
32    37    40    41    53    60    64    77    78    89    99
46    48    49    50    51    59    66    75    86    94    95
59    64    73    82    85    88    92    93    94    95    98
33    42    48    49    51    53    66    78    81    88    92
56    58    59    61    71    75    82    84    86    91    98
56    57    60    64    65    67    68    73    85    89    96
61    68    69    74    78    84    85    86    87    93    96
40    42    43    49    60    61    71    72    79    97    100
43    44    48    52    53    64    68    73    79    94    100
42    49    66    67    76    79    80    83    92    94    95
39    49    52    56    60    71    76    82    86    90    93
44    45    51    54    58    61    66    77    79    82    89
42    47    48    49    53    54    60    67    85    88    99
57    58    65    69    71    76    78    85    86    88    89
43    44    50    52    55    73    74    82    97    98    99
49    53    55    56    57    58    61    66    83    98    99
33    38    44    52    57    70    72    78    81    84    100
55    57    62    67    74    77    80    83    86    90    99
47    50    52    58    64    65    71    77    82    86    98
48    64    67    73    74    75    80    83    86    89    99
53    54    58    75    77    84    86    90    93    94    96
44    52    54    72    77    78    80    81    87    99    100
42    57    67    68    69    70    74    80    86    90    100
41    45    50    63    78    81    93    94    97    98    100
50    70    75    78    80    82    89    92    95    96    97
37    45    47    56    64    68    70    74    88    98    100
61    62    79    80    81    83    85    86    88    95    97
39    40    42    43    54    66    68    71    73    82    100
47    51    53    57    60    63    75    82    83    89    98
50    53    65    69    73    80    81    85    87    89    99
39    41    42    49    50    56    59    62    82    86    95
45    53    56    74    86    87    88    91    94    97    98
50    54    63    64    67    73    74    89    96    97    99
36    47    56    60    63    74    80    82    88    91    92
64    67    73    78    79    83    85    93    95    97    98
51    56    61    66    74    78    80    85    90    92    95
53    56    63    76    81    83    89    91    92    94    96
42    43    45    47    60    67    68    76    87    88    93
53    55    60    61    64    68    70    74    84    88    100
32    33    36    41    43    44    49    61    62    66    81
52    54    57    65    69    74    78    82    91    92    99
38    47    51    54    66    68    69    84    85    93    98
31    39    45    58    69    72    79    83    86    92    98
44    54    57    62    66    76    78    88    92    94    99
49    50    62    64    75    76    85    89    91    94    98
32    45    46    53    54    57    71    78    82    88    94
58    69    72    73    76    83    86    94    95    98    100
46    47    66    80    81    83    89    90    91    94    100
53    55    59    61    63    66    69    73    81    90    94
45    47    52    64    68    70    74    87    90    93    100
71    73    74    81    83    91    92    93    94    98    99
46    48    49    53    57    61    71    87    92    96    99
57    59    64    73    76    78    85    87    94    95    98
50    52    55    56    61    67    68    80    86    96    100
41    49    56    76    80    83    85    89    91    98    100
53    63    67    71    72    81    83    84    86    87    94
37    38    43    47    51    55    67    77    85    86    87
50    55    77    81    86    88    91    93    95    98    100
63    65    67    68    74    83    86    87    92    94    97
52    53    59    62    64    69    79    85    88    90    93
50    51    61    65    71    74    76    79    83    85    100
25    42    45    50    55    57    65    68    81    88    95
36    44    45    47    51    58    60    63    71    84    92
47    51    59    60    61    64    71    87    96    98    99
53    54    59    67    83    85    87    93    96    98    99
47    51    57    60    66    76    77    84    86    94    95
56    59    60    63    64    67    68    76    83    86    88
52    55    57    62    66    73    80    88    92    93    94
35    36    55    58    64    66    67    81    85    91    100
55    58    60    61    62    63    70    86    89    90    92
52    53    54    69    74    77    81    82    84    90    94
47    54    59    60    67    72    83    84    88    90    94
55    57    69    72    79    84    85    86    87    91    94
42    51    62    64    65    70    73    75    86    94    95
44    45    49    50    51    55    57    63    85    92    97
55    63    64    68    70    72    73    76    84    89    96
38    39    42    53    62    69    82    89    90    92    97
48    51    56    58    60    66    67    77    90    91    95
30    33    39    49    51    59    68    74    80    82    83
57    62    64    70    72    76    85    89    91    94    95
58    61    72    77    85    86    91    92    95    97    99
41    44    49    50    53    64    65    68    70    71    85
28    34    41    48    54    72    76    86    87    93    100
40    55    68    72    75    77    78    82    87    88    97
38    40    45    53    54    61    62    64    69    75    91
44    48    53    59    61    62    67    85    86    90    97
49    50    65    77    79    80    89    92    94    98    100
53    61    62    63    65    66    67    72    75    81    92
58    59    61    71    72    79    82    86    88    96    98
58    60    61    63    67    74    78    88    94    97    100
37    38    41    52    71    81    83    84    87    88    91
44    48    49    50    71    74    76    80    88    90    91
32    38    39    40    47    56    63    72    84    88    92
45    46    47    53    56    59    64    80    82    83    92
45    46    48    64    65    66    67    73    74    99    100
41    53    56    60    63    65    74    82    83    90    92
45    50    52    55    64    69    73    79    81    82    87
31    35    47    48    56    61    74    75    77    84    94
59    69    70    71    73    75    82    89    91    99    100
36    43    58    59    77    82    84    85    86    88    95
54    58    60    64    65    66    69    76    81    85    90
40    41    59    64    67    69    70    76    81    84    95
62    66    68    69    71    77    82    86    92    94    100
37    38    40    41    46    56    63    68    83    96    97
45    48    54    59    62    65    67    71    78    79    86
47    49    52    57    58    59    74    84    86    88    97
41    43    44    47    60    65    76    78    80    84    90
38    41    44    49    51    52    76    84    85    94    99
47    48    55    62    69    70    75    80    90    94    98
42    45    49    51    58    71    74    76    79    86    96
52    53    57    62    66    71    75    79    86    92    95
38    47    48    55    63    65    67    71    73    85    99
54    58    59    60    68    78    80    84    86    95    97
47    50    53    54    55    59    71    80    87    89    91
54    55    64    65    69    74    80    81    82    87    91
48    52    53    54    58    69    77    79    81    82    88
43    49    51    55    65    66    71    75    85    89    93
50    51    56    57    60    62    65    78    83    92    95
51    56    66    79    84    87    89    90    92    93    99
51    54    55    60    63    68    82    89    94    96    98
48    53    54    58    69    76    78    80    81    82    83
51    59    60    62    66    74    81    85    87    95    99
43    45    46    50    57    64    70    76    79    85    99
57    59    60    67    73    75    87    88    92    93    96
40    48    56    59    65    73    81    82    85    88    98
43    45    49    60    77    81    84    85    91    98    100
49    50    55    58    63    66    70    82    85    90    93
37    38    39    48    50    52    54    55    79    89    96
48    50    51    56    73    78    81    84    85    90    94
51    52    60    66    73    75    81    83    86    87    95
50    51    63    64    67    69    71    75    76    79    97
51    55    62    65    66    73    79    81    88    92    98
32    33    34    37    38    42    47    50    70    72    93
48    50    59    66    68    77    82    85    91    95    98
54    57    59    62    63    66    81    84    86    88    90
55    56    62    63    66    70    72    86    89    93    98
55    61    65    67    68    73    74    75    79    86    96
48    56    62    65    66    68    83    89    92    94    100
55    58    60    61    69    73    80    82    83    92    98
61    62    70    74    80    81    83    86    91    95    98
33    47    52    53    59    69    78    81    83    85    96
59    63    64    69    73    77    80    81    84    87    96
46    49    60    61    65    67    71    76    82    89    92
38    62    70    72    76    77    81    87    89    92    94
52    57    59    63    65    76    82    86    95    96    99
40    46    53    55    57    69    77    79    83    89    100
54    55    59    65    68    75    78    91    94    95    96
56    61    65    66    69    73    77    89    97    98    100
57    60    62    69    71    76    81    84    86    91    92
46    52    53    55    56    57    67    71    73    91    95
32    43    44    47    51    52    57    63    73    84    88
41    58    63    68    69    72    76    83    91    92    100
57    63    64    75    77    82    83    84    91    92    98
50    53    55    65    72    75    76    82    89    91    100
39    42    43    54    71    77    80    83    88    89    98
42    44    48    51    59    85    91    94    97    98    100
48    50    52    58    61    64    65    73    83    84    99
51    54    55    56    64    71    72    74    76    77    95
41    49    50    63    71    72    78    82    93    95    97
44    55    61    62    65    67    69    84    85    93    100
54    59    62    64    65    73    74    79    84    96    97
50    58    72    86    88    92    93    94    95    96    100
56    58    60    66    72    73    78    81    83    94    98
38    40    62    64    68    69    88    90    94    96    97
42    44    55    58    76    79    80    83    84    88    93
57    62    64    71    78    86    90    92    94    95    100
47    48    49    51    52    74    77    79    81    83    86
46    49    51    53    81    83    84    87    88    90    100
36    47    50    53    68    69    75    79    80    88    90
54    56    61    65    68    69    75    77    80    86    88
49    54    59    61    66    70    74    86    95    96    97
46    48    62    63    65    72    76    77    85    87    93
45    48    49    51    52    53    54    61    66    81    92
34    35    38    48    51    58    66    78    84    93    97
38    42    52    54    57    58    61    90    91    92    99
59    64    65    66    68    76    77    78    85    92    93
38    43    47    68    70    80    83    85    89    91    99
56    58    66    74    76    79    82    84    90    93    97
61    62    64    74    75    82    83    87    88    89    96
60    61    62    64    70    73    80    81    82    85    96
67    68    71    72    74    83    84    88    90    91    99
46    49    50    55    59    62    67    70    88    89    96
57    64    71    74    75    76    80    81    85    86    99
44    49    58    59    62    65    67    76    80    89    99
39    46    64    65    70    71    74    77    80    81    90
44    59    60    66    68    69    77    87    90    95    96
53    55    57    64    76    77    85    88    90    94    97
57    60    66    70    76    77    79    83    84    86    98
44    46    47    50    59    60    61    64    70    87    97
40    43    44    46    47    54    61    80    89    96    99
44    47    54    55    56    57    67    71    72    78    82
50    54    56    65    66    70    74    75    90    96    99
56    62    64    65    67    69    73    74    85    92    98
42    44    47    52    53    61    62    64    77    82    99
59    60    72    74    75    76    77    82    95    96    99
60    61    65    68    70    72    78    79    80    87    95
42    58    65    72    75    76    77    78    83    91    94
45    48    49    56    62    69    72    73    74    81    87
46    48    49    51    55    66    78    84    91    94    98
50    53    62    65    75    79    89    92    96    97    99
48    52    58    72    75    83    92    94    95    97    99
33    41    43    45    55    64    65    67    74    81    90
36    41    44    49    54    60    64    65    66    78    99
29    32    33    42    46    49    52    64    68    87    92
45    58    61    68    69    71    74    78    85    94    97
56    60    62    64    67    68    71    86    93    94    96
64    66    75    78    81    84    85    91    92    94    98
62    63    64    65    74    79    81    83    85    87    91
34    38    41    48    51    53    69    70    80    87    100
33    41    49    53    59    63    68    69    73    87    99
56    57    64    70    72    78    79    94    97    98    99
61    66    67    73    78    85    87    90    91    92    96
50    67    70    77    78    82    83    87    92    95    96
33    36    47    48    49    60    63    68    78    81    91
32    44    45    50    51    58    62    83    84    92    93
48    64    67    69    75    83    84    87    90    91    95
54    55    58    70    74    84    85    90    97    98    99
51    60    71    72    73    76    77    84    87    88    89
35    39    54    55    56    58    73    83    87    88    98
33    35    45    50    53    55    65    70    76    87    97
66    68    72    76    77    78    86    90    92    94    96
49    54    55    60    70    82    87    91    95    98    100
42    43    48    55    58    68    69    75    76    93    95
43    44    53    55    71    73    74    78    84    90    97
44    53    54    62    65    82    86    90    93    96    100
42    44    48    52    66    69    74    81    82    86    89
48    53    56    58    60    71    78    80    88    89    94
57    59    64    65    67    85    91    92    95    97    99
53    62    65    66    69    72    76    85    94    98    100
38    47    53    57    61    72    73    74    85    87    88
24    48    52    54    62    66    72    76    77    95    99
43    49    50    51    60    66    70    74    77    78    80
65    66    68    70    71    72    73    75    77    97    100
39    48    54    58    65    73    77    90    92    98    99
62    66    67    68    74    76    86    88    89    91    93
58    59    63    64    73    77    78    79    80    87    94
54    60    63    66    67    68    72    75    82    84    100
50    51    56    58    61    62    67    68    90    92    94
44    47    50    54    61    64    80    92    93    95    100
53    54    61    64    69    84    92    94    97    98    99
53    57    59    78    79    83    85    86    93    96    100
52    53    57    58    59    63    68    73    76    77    81
53    58    60    69    74    76    85    86    88    90    95
48    62    69    74    76    78    80    83    93    97    99
50    51    55    57    61    69    71    72    85    89    96
38    40    43    54    60    63    64    72    73    85    91
40    48    54    56    57    59    67    69    83    91    95
39    43    46    55    58    61    69    71    95    97    100
 
16 11 88 32 25 0 70 78 73 61 90 89 46 95 6 33 34 21 14 22
76 63 18 44 84 11 55 93 8 29 82 62 17 64 24 14 37 97 49 51
34 8 10 11 99 28 67 20 55 74 45 48 85 91 79 57 47 22 17 54
14 67 12 55 25 60 51 30 41 35 78 83 93 64 98 39 44 49 5 63

for example, I'd like to know:
+ Which are, and how many are, pairs of numbers with the precise difference of 19 within each row in the above table.
+ I am using Excel-2010

Is it possible to do it in Visual Basic in MS EXCEL 2010? How can it be done?


I'm looking forward to receiving good news.

Thanks in advance

[codegazer]

I have no experience working with VB in Excel, but this VB6 code snippet may give you a start.
This assumes the data is in a two-dimensional grid.

'set numbers here to suit
 
dim maxrow as integer   'for rows
dim maxcol as integer   'for column
 
'example maximum figures
maxrow = 100: maxcol = 40
 
dim row(maxrow) as integer                  'row
dim col(maxcol) as integer                  'column
dim listnumber(maxrow, maxcol) as integer   'data
 
dim a as integer
dim b as integer
dim n as integer
 
for a = 1 to maxrow          'work each row
    for b = 1 to maxcol-1      '
        n = b
        While n<= maxcol     'compare each figure in row against all others
            n = n + 1
            if abs(listnumber(a,n)-listnumber(a,b))=19 then  'use abs in case of -ve number
                print "Row: ";a; "  Columns:";b; "  and ";n
            endif
        wend
    next b
next a

[SANDRADEPAR]

yeah u got the idea into mind: exact difference of two numbers or ABS is 19. i saw that u wrote it in the code. I TRIED your code in MS EXCEL 2010, but it returned no results, because it displayed error messages: error compilations in the code.
I got almost-nothing skills in programming, I recently have begun study vba 4/5/6 windowns a month ago.

Would you fix the code up, please?

many thanks

[Rabbit]

Please use code tags when posting code or formatted data.

Your thread has been moved to the excel forum.

You should be aware that this is not a code writing service. We will help you fix code but most of us do not have the time to write it all for you. We expect you to put forth some effort.

[SANDRADEPAR]

thks for your help.

Would you fix the code up, please?

many thanks in advance

[Rabbit]

You haven't posted any code

[codegazer]

Sandradepar - As stated at the top of my reply, I have no experience of VB in Excel. As far as I'm concerned, I tried to help in writing the formulae to scan the data and output the results to give you a start, but I'm not contracted to you and it's up to you to work out how to reference the data.

[codegazer]

Sandradepar,
I don't know if you've got any further, but I felt I might offer you a clue. It isn't difficult stuff.

I don't do Excel VBA, but a 5-minute google will show that you can reference the cells quite easily so that line 22 might read:-

22.                 if abs(Worksheets("Sheet1").Cells(a,n) - Worksheets("Sheet1").Cells(a,b))=19 then    'use abs in case of -ve number

[zmbd]

codegazer,
Thank you for your efforts on this... the ball is in SANDRADEPAR's court to make an effort to apply your solution(s) to the situation and show their effort here for more guidance.

We're not a code writing service.

[SANDRADEPAR]

i tried this code but i got limited results (the code omitting some results i dont know how to fix this:

Sub difference()
 
Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim lngMatch As Long
 
Dim arrMatch(100) As Variant
Dim arrMatchValues(100) As Variant
Dim arrMatchValues2(100) As Variant
 
Const valDif = 19
 
lngMatch = 0
 
Sheets("Plan1").Select
 
lngLastRow = Sheets("Plan1").Range("A1").End(xlDown).Row
lngLastColumn = Sheets("Plan1").Range("A1").End(xlToRight).Column
 
 
For i = 1 To lngLastRow
    For j = 1 To lngLastColumn
            If Cells(i, j + 1).Value - Cells(i, j).Value = valDif Then
 
                arrMatch(lngMatch) = Cells(i, j).Address
                arrMatchValues(lngMatch) = Cells(i, j + 1).Value
                arrMatchValues2(lngMatch) = Cells(i, j).Value
 
                lngMatch = lngMatch + 1
 
            End If
    Next j
Next i
 
Sheets("Plan2").Select
 
Cells(1, 1).Value = lngMatch & " records found"
 
For i = 0 To lngMatch
 
    Cells(i + 1, 3).Value = arrMatch(i)
    Cells(i + 1, 5).Value = arrMatchValues(i)
    Cells(i + 1, 6).Value = arrMatchValues2(i)
 
Next i
 
End Sub
 
 

try to aplly it in a small sample :

54    60    63    66    67    68    72    75    82    84    100
50    51    56    58    61    62    67    68    90    92    94
44    47    50    54    61    64    80    92    93    95    100
53    54    61    64    69    84    92    94    97    98    99
53    57    59    78    79    83    85    86    93    96    100
52    53    57    58    59    63    68    73    76    77    81
53    58    60    69    72    79    85    86    88    91    98
48    62    69    74    76    78    80    83    93    97    99
50    51    55    57    61    69    71    72    85    89    96
38    40    43    59    60    63    64    72    73    85    93
40    48    54    56    57    59    67    69    73    86    92
39    43    46    55    58    62  74    82    95    97    100

the code is omitting some results in some lines (it displays only 1 pair per line/row, and no more results) i dont know how to fix that.

Also I have absolutely no experience of VB in Excel.
can someone fix the code up, please?
Does anyone have a better practical idea that solve the problem( in the 1st post on top of the page) more easily ??? Any help is so welcome.

many thanks in advance

[zmbd]

Let us go back to the dataset in your first-post

Just row one....
34    46    61    70    73    78    88    89    90    95    100

Option 1:
|A-B|=|C| such that:
|34-46|=|12|; |34-61|=|27|; |34-70|=|27| etc...
|46-61|=|15|; |46-70|=|24|; |46-73|=|27| etc...
and so forth thru all of the possible combinations of those numbers with only the given row,

OR

Option 2:
A-B, B-C, C-D so that:
|34-46|; |46-61|; |61-70|; etc...

IN either option 1 or 2, returning only those numbers where their absolute difference is 19 within the given row.

Which option is correct?

[SANDRADEPAR]

friend, thanks for your help .
yeah that's the main idea : to find the exact difference of two numbers or their absolute value |A-B|=|C| , that should be 19.

the code that i posted previously is omitting some results in some lines (it displays only 1 pair per line/row, and no more results) i dont know how to fix that.
i tried valDif in the code such as A-B, B-C, C-D, but it is omitting some results in some lines (it displays only 1 pair per line/row, and no more results) .
try to aplly it in a small sample :


54 60 63 66 67 68 72 75 82 84 100
50 51 56 58 61 62 67 68 90 92 94
44 47 50 54 61 64 80 92 93 95 100
53 54 61 64 69 84 92 94 97 98 99
53 57 59 78 79 83 85 86 93 96 100
52 53 57 58 59 63 68 73 76 77 81
53 58 60 69 72 79 85 86 88 91 98
48 62 69 74 76 78 80 83 93 97 99
50 51 55 57 61 69 71 72 85 89 96
38 40 43 59 60 63 64 72 73 85 93
40 48 54 56 57 59 67 69 73 86 92
39 43 46 55 58 62 74 82 95 97 100


You'll see that some results are missing

Also I have very small limited knowledge of VB in Excel.

many thanks

[codegazer]

To be clear, can you confirm that you are trying to find ANY two numbers in each row that differ by 19?

[zmbd]

OK, your mixing formulas again....

absolute value |A-B|=|C| , that should be 19.
(...) i tried valDif in the code such as A-B, B-C, C-D, but it is omitting some results in

However, it appears that you are after Option2

So let's look at the logic:
(I so highly advise that you learn how to properly chart your prgrams, I prefer Nassi-Shneiderman Charts (PDF) for most, if not all, my programming logic)

Now what I would do is set your reference so that [R1C1] actually refers to the start of your dataset and then offset from there checking for null/isblank as one went.

so the logic
+ [Start]
+ [find first cell in data set]
+ [set a pointer to that cell]=[R1C1]
+ [x=0]
+ [n=0]
+ <Start loop - Rows in datatable>
++<Start loop calculations>
+++[value of first cell][R1C1]offset(R+x,C+n)=lcell
+++[value of second cell][R1C1]offset(R+x,C+(n+1))=rcell
+++ [compare lcell-rcell = |19| report pair (to?) if true]
+++ [increment your column offset n]
+++ <end loop calculations if either cell offset returns empty cell>
++ [increment your row offset x]
++ [reset the column offset n to 0]
++ <end loop rows if the offset returns empty cell>
+ [end]

See if the logic chart and the afore mentioned logic will get you to the right solution.

As a note:
The following is copyright by MS Original Link is here: https://support.microsoft.com/en-us/kb/269866 however support is ended for this link so I copy part of it here for academic reasons:

A1 Reference Style vs. R1C1 Reference Style

The A1 Reference Style
By default, Excel uses the A1 reference style, which refers to columns as letters (A through IV, for a total of 256 columns), and refers to rows as numbers (1 through 65,536). These letters and numbers are called row and column headings. To refer to a cell, type the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, type the reference for the cell that is in the upper-left corner of the range, type a colon (:), and then type the reference to the cell that is in the lower-right corner of the range.
The R1C1 Reference Style
Excel can also use the R1C1 reference style, in which both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful if you want to compute row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

[SANDRADEPAR]

many thanks to all,
codegazer i 'm trying to find ALL POSSIBLE SOLUTIONS THAT two numbers in each row differ by 19 or
its absolute value is 19 such as 58 - 39 = 19 or |58 - 39|= 19 or |39 - 58|= 19

the code that i posted above is omitting some results in some lines (it displays only 1 pair per line/row in final results, and no more results, it is excluding some results, and i don't know why) and i dont know how to fix that.

as I have said above i have very small limited knowledge of VB in Excel.
Does anyone have a better pragmatical approach that solve the problem more easily ??? Any help is so welcome.

many thanks in advance

[zmbd]

SANDRADEPAR:

+ We understand your question.

+ What we are waiting for is for you to tell us what it is that you are after. One time it's one thing, the next it appears you want something else.

+ Using the pseudo-code I gave you in post#14, it took me all of 20 minutes, and most of that was hand-checking against your dataset in the original post, to write the script for comparing the adjacent pairs - something you already have. If this isn't what you're after then you need to tell us what you've tried to do to fix the script

[codegazer]

Come on, Sandradepar.
You were very nearly there when you posted your code a week ago, but your code only ever compares adjacent pairs because you use j & j+1.
To find all results in each row, you will have to search from j to the end of the row

[zmbd]

codegazer, I think that is what SANDRADEPAR is after...
(from the post#12, using the data in post #1)
34-46, 46-61, 61-70, 70-73....
49-51, 51-55, 55-62, 62-63....

Which in this data set (in post#1) only occur 8 times and then only once in the rows where the pairs have a difference of 19 - and they are all negative in nature :)
It may be the case that SANDRADEPAR has one solution to the question at hand; however, SANDRADEPAR needs to clarify the actual goal.

What clouds the issue for me is the rows of numbers at the bottom of the example data in post#1:
16 11 || 88 3 || 2 25 0 || 73 61...

[SANDRADEPAR]

many thanks to all, codegazer and zmbd

What i'm trying to say (the main goal) is that to find ALL POSSIBLE SOLUTIONS THAT any two numbers within each line/row differ by 19 or its absolute value is 19 such as 58 - 39 = 19 or |58 - 39|= 19 or |39 - 58|= 19 for each line/row.

the code that i posted above is omitting some results in some lines (it displays only 1 pair per line/row in final results, and no more results, it is excluding some results, and i don't know why) and i dont know how to fix that.
See the code:

   Sub difference()
 
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim lngMatch As Long
 
    Dim arrMatch(100) As Variant
    Dim arrMatchValues(100) As Variant
    Dim arrMatchValues2(100) As Variant
 
    Const valDif = 19
 
    lngMatch = 0
 
    Sheets("Plan1").Select
 
    lngLastRow = Sheets("Plan1").Range("A1").End(xlDown).Row
    lngLastColumn = Sheets("Plan1").Range("A1").End(xlToRight).Column
 
 
    For i = 1 To lngLastRow
        For j = 1 To lngLastColumn
                If Cells(i, j + 1).Value - Cells(i, j).Value = valDif Then
 
                    arrMatch(lngMatch) = Cells(i, j).Address
                    arrMatchValues(lngMatch) = Cells(i, j + 1).Value
                    arrMatchValues2(lngMatch) = Cells(i, j).Value
 
                    lngMatch = lngMatch + 1
 
                End If
        Next j
    Next i
 
    Sheets("Plan2").Select
 
    Cells(1, 1).Value = lngMatch & " records found"
 
    For i = 0 To lngMatch
 
        Cells(i + 1, 3).Value = arrMatch(i)
        Cells(i + 1, 5).Value = arrMatchValues(i)
        Cells(i + 1, 6).Value = arrMatchValues2(i)
 
    Next i
 
    End Sub
 
 

And try to apply it in a small sample :

54 60 63 66 67 68 72 75 82 84 100
50 51 56 58 61 62 67 68 90 92 94
44 47 50 54 61 64 80 92 93 95 100
53 54 61 64 69 84 92 94 97 98 99
53 57 59 78 79 83 85 86 93 96 100
52 53 57 58 59 63 68 73 76 77 81
53 58 60 69 72 79 85 86 88 91 98
48 62 69 74 76 78 80 83 93 97 99
50 51 55 57 61 69 71 72 85 89 96
38 40 43 59 60 63 64 72 73 85 93
40 48 54 56 57 59 67 69 73 86 92
39 43 46 55 58 62 74 82 95 97 100

You'll see that some results are missing because as my friend codegazer said:

codegazer: " your code only ever compares adjacent pairs because you use j & j+1.
To find all results in each row, you will have to search from j to the end of the row".

The problem with my code is what u said above, but i'm not able to fix that, because I have very small limited knowledge of VB in Excel.

Can help me?

many thanks

[codegazer]

You are nearly there.
A couple of adjustments as below should show the results

For i = 1 To lngLastRow
 For j = 1 To lngLastColumn-1
    'extra loop to search to end of row
   for n= j+1 to lnglastcolumn
     If abs(Cells(i, n).Value - Cells(i, j).Value) = valDif Then
 
       arrMatch(lngMatch) = Cells(i, j).Address
       arrMatchValues(lngMatch) = Cells(i, n).Value
       arrMatchValues2(lngMatch) = Cells(i, j).Value
 
       lngMatch = lngMatch + 1
 
     End If
   Next n
 Next j
Next i
 

[zmbd]

Attached is a file with one approach to the solution:

To use:
>always scan files with your antivirus.
Select the upper-right-hand cell of the data-table.
Sheet1!A1
Excel2013>Ribbon>View>Macros>ViewMacros
[LoopAndCompareAdjecentCells]
Does just that, with a row of data compares the adjacent cells
a-b, b-c, c-d, etc...
Repeating for each row

[LoopAndCompareWithinRow]
Does just that, takes each cell in turn and compares with the remaining cells within the row
a-b, a-c, a-d.... b-c, b-d, b-e,.... c-d, c-e, c-f, etc....
Repeating for each row

Following is the source code for module, insert a standard module or copy and paste the following in to the "ThisWorkbook" module of the workbook:

All of the output is being sent to the Immediate pane - open with <ctrl><g>
>> This pane will scroll thru very quickly so not every result will be visible in the pane
LINE 27 and LINE 71
Are the comparison lines for the ABS(LCell-RCell)=19
These are one of the lines you will need to modify to store your results where you need them... I did not code this because you didn't indicate what you wanted done with this information.

Personally, I would either open a text file for output and write the results or insert a new worksheet and insert the values there as found.

Option Explicit 'DO NOT DUPLICATE THIS LINE IN YOUR MODULE
 
Sub loopandcompareadjecentcells()
Dim zDifference As Long
Dim zStartCell As Range
Dim zLcell As Range
Dim zRcell As Range
Dim zrowoffset As Long
Dim zclmoffset As Long
'
'using the currently selected cell as the staring point
zrowoffset = 0
zclmoffset = 0
'
'set up for the first compair and then enter the loop
Set zStartCell = ActiveCell
Set zLcell = zStartCell
Set zRcell = zLcell.Offset(zrowoffset, (zclmoffset + 1))
'
'Drop out of the procedure if the first cell is empty other wise continue until the first empty cell is found
Do Until IsEmpty(zLcell)
'Row loop
'Drop out of the loop is the cell is empty else continue until the first empty cell is found
Debug.Print "row= " & zLcell.Row
    'loop until the first empty cell is found
    Do Until IsEmpty(zLcell) Or IsEmpty(zRcell)
        If Abs((zLcell.Value - zRcell.Value)) = 19 Then Debug.Print Tab(10); zLcell.Value & "-" & zRcell.Value & "="; (zLcell.Value - zRcell.Value)
        zclmoffset = zclmoffset + 1
        Set zLcell = zStartCell.Offset(zrowoffset, zclmoffset)
        Set zRcell = zStartCell.Offset(zrowoffset, (zclmoffset + 1))
    Loop
    zrowoffset = zrowoffset + 1
    zclmoffset = 0
    Set zLcell = zStartCell.Offset(zrowoffset, zclmoffset)
    Set zRcell = zStartCell.Offset(zrowoffset, (zclmoffset + 1))
Loop
If Not zStartCell Is Nothing Then Set zStartCell = Nothing
If Not zLcell Is Nothing Then Set zLcell = Nothing
If Not zRcell Is Nothing Then Set zRcell = Nothing
Debug.Print "done"
End Sub
 
Sub loopandcomparewithinrow()
Dim zDifference As Long
Dim zStartCell As Range
Dim zLcell As Range ' cell on the left ( left - right = value )
Dim zRcell As Range ' cell on the right ( left - right = value )
Dim zrowoffset As Long ' Which row in the data table
Dim zleftoffset As Long ' pointer relative to the table start
Dim zrightoffset As Long ' pointer relative to the table start
'
zrowoffset = 0 ' Start in the first row where the currently selected cell is located
zleftoffset = 0 ' Using the currently slected cell as the starting point in the row
zrightoffset = 1 ' pull the adjcent cell to the starting cell
'
'set up for the first compair and then enter the loop
Set zStartCell = ActiveCell
Set zLcell = zStartCell
Set zRcell = zLcell.Offset(zrowoffset, (zleftoffset + zrightoffset))
'
'now if there isn't anything in the first cell then drop out of the procedure otherwise keep going until we have an empty cell
Do Until IsEmpty(zLcell)
'Row loop
Debug.Print "row= " & zLcell.Row
    'Start left cell loop
    'once again, drop out if nothing is found in the cell else keep going until the first empty cell
    Do Until IsEmpty(zLcell)
        'Start right loop
        'once again, drop out if nothing is found in the cell else keep going until the first empty cell
        Do Until IsEmpty(zRcell)
            If Abs((zLcell.Value - zRcell.Value)) = 19 Then Debug.Print Tab(10); zLcell.Value & "-" & zRcell.Value & "="; (zLcell.Value - zRcell.Value)
            zrightoffset = zrightoffset + 1
            Set zRcell = zStartCell.Offset(zrowoffset, (zleftoffset + zrightoffset))
        Loop 'right cell
        zleftoffset = zleftoffset + 1
        zrightoffset = 1
        Set zLcell = zStartCell.Offset(zrowoffset, zleftoffset)
        Set zRcell = zStartCell.Offset(zrowoffset, (zleftoffset + zrightoffset))
    Loop 'left cell
    zrowoffset = zrowoffset + 1
    zleftoffset = 0
    Set zLcell = zStartCell.Offset(zrowoffset, zleftoffset)
    Set zRcell = zStartCell.Offset(zrowoffset, (zleftoffset + 1))
Loop 'row
If Not zStartCell Is Nothing Then Set zStartCell = Nothing
If Not zLcell Is Nothing Then Set zLcell = Nothing
If Not zRcell Is Nothing Then Set zRcell = Nothing
Debug.Print "done"
End Sub

[SANDRADEPAR]

thank you codegazer ,
i inserted the adjustments that u provided me above into the code:

 Sub difference()
 
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim lngMatch As Long
 
    Dim arrMatch(100) As Variant
    Dim arrMatchValues(100) As Variant
    Dim arrMatchValues2(100) As Variant
 
    Const valDif = 19
 
    lngMatch = 0
 
    Sheets("Sheet1").Select
 
    lngLastRow = Sheets("Sheet1").Range("A1").End(xlDown).Row
    lngLastColumn = Sheets("Sheet1").Range("A1").End(xlToRight).Column
 
 
     For i = 1 To lngLastRow
 For j = 1 To lngLastColumn - 1
    'extra loop to search to end of row
   For n = j + 1 To lngLastColumn
     If Abs(Cells(i, n).Value - Cells(i, j).Value) = valDif Then
 
       arrMatch(lngMatch) = Cells(i, j).Address
       arrMatchValues(lngMatch) = Cells(i, n).Value
       arrMatchValues2(lngMatch) = Cells(i, j).Value
 
       lngMatch = lngMatch + 1
 
     End If
   Next n
 Next j
Next i
 
    Sheets("Sheet2").Select
 
    Cells(1, 1).Value = lngMatch & " records found"
 
    For i = 0 To lngMatch
 
        Cells(i + 1, 3).Value = arrMatch(i)
        Cells(i + 1, 5).Value = arrMatchValues(i)
        Cells(i + 1, 6).Value = arrMatchValues2(i)
 
    Next i
 
    End Sub
 

I tried it several times , but nothing happened.
Can you see if i made the aproppriate adjustments right in the code?

merry christmas

God bless you forever

[SANDRADEPAR]

many thanks for your help zmbd,
i try to execute your 964972.zip file so many times in my EXCEL 2010.But it didn't work.
I run loopandcompareadjecentcells first, and after i run LoopAndCompareWithinRow . Nothing happened.
It didn't write any results.
All the test that i made in EXCEL 2010 were unsuccessfull.
I don't know if the codes of loopandcompareadjecentcells and LoopAndCompareWithinRow are compatible with old EXCEL 2010 ...
( i can't afford for a new whole OFFICE 2015 nowadays, cause i 'm broken)

Happy christmas

God bless you ad aeternum

[zmbd]

1) Forgive me for the stupid tech-support questions: When you downloaded the file did you unzip it?

2) This code compiles and runs on a 2007, 2010, and 2013 - 32 and 64bit office installs. (Have a friend with 2007, I have a runtime 2010 on the laptop, and bit 32 and 64bit versions of 2013 on the desktop PCs (although the 64bit is going to be reformatted by my IT-staff over the new year)

3) These are not functions
This code is designed to take the activecell and use that as a starting point.
If you unzip the file, open in Excel, select sheet one, cell A1, and run either of the VBA scripts, it will work (see the instructions)

4) The output is to the Debug window, press <alt><F11> to open the editor, <ctrl><g> to open the immediate pane (IP).

4a) Because the dataset is so large, the output to the IP will scroll off of the top; thus, you will not see all of the results.

4b) To store the results you will need to modify the debug.print lines to store these in the desired location.

5) the actual script is in the post's code block.

-z