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.microso ft.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