VBA: Refactoring a code - Looping trend formula.

montecarlo2012 · Jul 19, 2020

Hello. Everyone.

By the time I requested this code the parameters were fine, now a new approach is require
This is the code:

VBA Code:

Sub third_order_polynomial()

Application.ScreenUpdating = False

         Dim rngStart As Range, rngData As Range
         Dim Diff1&, Diff2&, NoRows&, NoCols&, i&
         Dim s$
         
         Set Sht = Worksheets("Sheet1")
         Set rngData = Sht.Range("B3:G" & Sht.Cells(Rows.Count, "B").End(xlUp).Row)

                 
                           NoRows = rngData.Rows.Count
                           NoCols = rngData.Columns.Count
                           Diff1 = 8: Diff2 = 35
         Set rngStart = Range("I3").Resize(, NoCols)
                           For i = Diff1 To Diff2
                                    With rngStart.Offset(, (NoCols + 1) * (i - Diff1)).Resize(NoRows - i)
                                    
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

.Rows(0).Formula = "=SUMPRODUCT(--(" & rngData.Resize(NoRows - i, 1).Address(0, 0) & "=" & .Columns(1).Address(0, 0) & "))"
.Rows(0).Font.Bold = True

 '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                                                         
     ''THIRD ORDER POLYNOMIAL
            'Equation: Y = (C3 * X^3) + (C2 * X^2) + (C1 * X^1) +B
            'B: = INDEX(LINEST(Y, X^{1,2,3}),,1,4)
            'FORMULA = " = TRUNC(ABS(INDEX(LINEST(B3:B19,$A$3:$A$19^{1,2,3}),1,4)
                                                                                        
  '////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
   
   .Formula = "=TRUNC(ABS(INDEX(LINEST(" & rngData.Resize(i + 1, 1).Address(0, 0) & "," & rngData.Offset(, -1).Resize(i + 1, 1).Address(0, 1) & "^{1,2,3}),4)))"
               
 '///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                                                        
                                                         s = .Cells(1, 1).Address(0, 0)
                                             With .FormatConditions
                                                      .Delete
                                                      .Add Type:=xlExpression, Formula1:="=" & rngData(1, 1).Address(0, 0) & "=" & s
                                                      .Item(1).Interior.Color = vbYellow
                                             End With
                                    End With
                           Next i
          Application.ScreenUpdating = True
End Sub

Tracing precedents the code show this

In words this picture say, the index-linest are calculated by sections and in this one is B3:B11 and compare the results with B3:G6 and highlight the matches on array I:N, but now a new interpretation or logic is founded
This is the favor, Please.
The same calculations yes, but the match idea is the difference here, instead of B3:B11, is necessary B4:B12 compare the results against B3:G3 and the results on I2 be display on “sheet 2. B2”
Just in case somebody give me a hand here please,

buy the way, the code here was provided by Mr. Stephen Crump (thanks for that Sir.)in the post number 1131286 for more information how everything started.

In the new request the trace precedents are like

So if I compare B3:G3 against I2:N2 the are not matches
And the results on row 2 [I:N] to [GP:GU]
For illustration purpose about the data on row 2 I did this way

the sample sheet is here:
<tbody>


						197	128	122	113	127	157	194	163	120	143	132	157	196	138	119	113	117	155	194	140	115	149	123	157
5	15	18	34	37	47	8	18	24	33	40	46	7	18	24	33	40	47	7	18	25	33	40	47	7	18	24	32	39	47
6	23	32	33	42	49	8	19	25	33	40	47	7	18	26	33	40	47	7	18	25	32	39	47	7	17	24	31	38	47
16	30	38	43	51	53	7	17	25	33	40	47	7	17	24	32	39	46	7	16	23	31	37	46	7	17	24	32	37	46
6	26	38	41	42	47	6	16	23	31	37	46	6	15	21	30	36	46	7	15	22	31	36	45	7	15	22	29	35	45
7	16	25	33	43	46	6	14	20	29	35	46	7	14	21	30	36	45	7	14	20	28	35	45	9	16	22	30	36	45
6	12	16	27	30	44	7	14	21	29	35	45	7	14	20	28	34	44	9	16	22	29	35	45	9	16	22	31	36	46
10	19	20	25	29	38	7	14	20	28	34	45	10	16	22	30	36	45	9	16	22	31	37	46	9	16	23	32	37	46
8	23	25	34	49	53	10	16	22	30	36	46	9	16	22	32	38	47	9	16	24	33	38	47	9	15	25	33	39	47
2	5	7	31	37	43	9	15	22	32	36	46	9	15	24	33	37	46	9	14	25	33	38	47	8	13	24	33	37	47
12	18	28	34	40	53	10	16	26	33	37	47	10	15	27	34	38	47	9	14	26	33	38	48	8	14	26	33	38	48
1	12	33	37	40	51	9	15	27	34	38	47	8	14	26	33	37	47	8	13	26	33	38	48	7	13	24	32	38	47
10	17	18	20	30	41	9	14	25	33	37	47	8	14	25	33	37	47	8	13	24	31	37	47	8	13	23	30	36	45
1	5	9	23	24	47	8	13	26	35	38	48	8	13	24	33	38	48	7	13	23	31	36	46	7	13	23	30	36	45
16	22	33	37	40	41	9	14	26	34	40	48	8	13	25	32	38	46	8	14	24	31	37	45	8	14	24	31	37	45
7	11	14	15	23	38	7	12	24	32	38	46	7	13	23	30	37	45	8	13	23	30	37	46	8	13	22	29	36	46
35	37	39	46	48	52	7	13	25	32	39	46	8	14	24	32	39	47	8	13	23	30	37	47	7	12	22	28	35	46
3	14	22	47	50	53	5	11	23	30	38	46	5	11	21	28	36	47	5	10	20	27	34	45	5	10	20	27	34	45
12	13	39	41	44	50	5	11	21	26	34	46	5	10	20	25	32	44	5	10	20	25	32	44	5	10	19	24	31	44
3	7	40	42	46	51	4	10	18	23	31	44	4	10	18	24	31	44	4	9	17	22	30	43	4	10	18	23	31	44
1	2	13	31	33	53	4	10	16	22	30	43	4	10	15	20	28	43	4	10	16	21	30	43	4	11	17	23	31	44
1	13	27	33	41	51	5	10	15	19	28	41	5	11	16	20	29	42	4	12	18	22	31	43	4	13	18	24	33	44
5	9	12	15	37	45	5	10	15	19	28	41	5	12	17	21	30	42	5	13	18	23	32	43	4	12	18	23	31	43
3	10	15	19	20	28	5	13	18	22	29	42	5	13	18	24	31	43	4	13	18	24	31	43	4	12	19	25	32	44
6	17	18	19	32	36	5	13	19	25	33	45	5	13	19	24	32	45	4	13	20	25	33	45	4	14	20	26	34	46
14	18	22	28	40	53	4	13	19	25	32	46	4	12	20	26	33	46	4	13	20	26	34	47	4	15	22	27	35	47
8	10	11	14	21	50	3	12	20	26	33	46	3	13	20	26	34	46	3	14	22	27	35	46	3	14	22	29	36	47

</tbody>

Thanks for reading this post.
Looking forward to hear from you guys.
Montecarlo2012

montecarlo2012 · Jul 21, 2020

VBA: Refactoring a code - Looping trend formula.

montecarlo2012

Well-known Member

Excel Facts

montecarlo2012

Well-known Member

Similar threads

Forum statistics

Share this page


						197	128	122	113	127	157	194	163	120	143	132	157	196	138	119	113	117	155	194	140	115	149	123	157
5	15	18	34	37	47	8	18	24	33	40	46	7	18	24	33	40	47	7	18	25	33	40	47	7	18	24	32	39	47
6	23	32	33	42	49	8	19	25	33	40	47	7	18	26	33	40	47	7	18	25	32	39	47	7	17	24	31	38	47
16	30	38	43	51	53	7	17	25	33	40	47	7	17	24	32	39	46	7	16	23	31	37	46	7	17	24	32	37	46
6	26	38	41	42	47	6	16	23	31	37	46	6	15	21	30	36	46	7	15	22	31	36	45	7	15	22	29	35	45
7	16	25	33	43	46	6	14	20	29	35	46	7	14	21	30	36	45	7	14	20	28	35	45	9	16	22	30	36	45
6	12	16	27	30	44	7	14	21	29	35	45	7	14	20	28	34	44	9	16	22	29	35	45	9	16	22	31	36	46
10	19	20	25	29	38	7	14	20	28	34	45	10	16	22	30	36	45	9	16	22	31	37	46	9	16	23	32	37	46
8	23	25	34	49	53	10	16	22	30	36	46	9	16	22	32	38	47	9	16	24	33	38	47	9	15	25	33	39	47
2	5	7	31	37	43	9	15	22	32	36	46	9	15	24	33	37	46	9	14	25	33	38	47	8	13	24	33	37	47
12	18	28	34	40	53	10	16	26	33	37	47	10	15	27	34	38	47	9	14	26	33	38	48	8	14	26	33	38	48
1	12	33	37	40	51	9	15	27	34	38	47	8	14	26	33	37	47	8	13	26	33	38	48	7	13	24	32	38	47
10	17	18	20	30	41	9	14	25	33	37	47	8	14	25	33	37	47	8	13	24	31	37	47	8	13	23	30	36	45
1	5	9	23	24	47	8	13	26	35	38	48	8	13	24	33	38	48	7	13	23	31	36	46	7	13	23	30	36	45
16	22	33	37	40	41	9	14	26	34	40	48	8	13	25	32	38	46	8	14	24	31	37	45	8	14	24	31	37	45
7	11	14	15	23	38	7	12	24	32	38	46	7	13	23	30	37	45	8	13	23	30	37	46	8	13	22	29	36	46
35	37	39	46	48	52	7	13	25	32	39	46	8	14	24	32	39	47	8	13	23	30	37	47	7	12	22	28	35	46
3	14	22	47	50	53	5	11	23	30	38	46	5	11	21	28	36	47	5	10	20	27	34	45	5	10	20	27	34	45
12	13	39	41	44	50	5	11	21	26	34	46	5	10	20	25	32	44	5	10	20	25	32	44	5	10	19	24	31	44
3	7	40	42	46	51	4	10	18	23	31	44	4	10	18	24	31	44	4	9	17	22	30	43	4	10	18	23	31	44
1	2	13	31	33	53	4	10	16	22	30	43	4	10	15	20	28	43	4	10	16	21	30	43	4	11	17	23	31	44
1	13	27	33	41	51	5	10	15	19	28	41	5	11	16	20	29	42	4	12	18	22	31	43	4	13	18	24	33	44
5	9	12	15	37	45	5	10	15	19	28	41	5	12	17	21	30	42	5	13	18	23	32	43	4	12	18	23	31	43
3	10	15	19	20	28	5	13	18	22	29	42	5	13	18	24	31	43	4	13	18	24	31	43	4	12	19	25	32	44
6	17	18	19	32	36	5	13	19	25	33	45	5	13	19	24	32	45	4	13	20	25	33	45	4	14	20	26	34	46
14	18	22	28	40	53	4	13	19	25	32	46	4	12	20	26	33	46	4	13	20	26	34	47	4	15	22	27	35	47
8	10	11	14	21	50	3	12	20	26	33	46	3	13	20	26	34	46	3	14	22	27	35	46	3	14	22	29	36	47


						197	128	122	113	127	157	194	163	120	143	132	157	196	138	119	113	117	155	194	140	115	149	123	157
5	15	18	34	37	47	8	18	24	33	40	46	7	18	24	33	40	47	7	18	25	33	40	47	7	18	24	32	39	47
6	23	32	33	42	49	8	19	25	33	40	47	7	18	26	33	40	47	7	18	25	32	39	47	7	17	24	31	38	47
16	30	38	43	51	53	7	17	25	33	40	47	7	17	24	32	39	46	7	16	23	31	37	46	7	17	24	32	37	46
6	26	38	41	42	47	6	16	23	31	37	46	6	15	21	30	36	46	7	15	22	31	36	45	7	15	22	29	35	45
7	16	25	33	43	46	6	14	20	29	35	46	7	14	21	30	36	45	7	14	20	28	35	45	9	16	22	30	36	45
6	12	16	27	30	44	7	14	21	29	35	45	7	14	20	28	34	44	9	16	22	29	35	45	9	16	22	31	36	46
10	19	20	25	29	38	7	14	20	28	34	45	10	16	22	30	36	45	9	16	22	31	37	46	9	16	23	32	37	46
8	23	25	34	49	53	10	16	22	30	36	46	9	16	22	32	38	47	9	16	24	33	38	47	9	15	25	33	39	47
2	5	7	31	37	43	9	15	22	32	36	46	9	15	24	33	37	46	9	14	25	33	38	47	8	13	24	33	37	47
12	18	28	34	40	53	10	16	26	33	37	47	10	15	27	34	38	47	9	14	26	33	38	48	8	14	26	33	38	48
1	12	33	37	40	51	9	15	27	34	38	47	8	14	26	33	37	47	8	13	26	33	38	48	7	13	24	32	38	47
10	17	18	20	30	41	9	14	25	33	37	47	8	14	25	33	37	47	8	13	24	31	37	47	8	13	23	30	36	45
1	5	9	23	24	47	8	13	26	35	38	48	8	13	24	33	38	48	7	13	23	31	36	46	7	13	23	30	36	45
16	22	33	37	40	41	9	14	26	34	40	48	8	13	25	32	38	46	8	14	24	31	37	45	8	14	24	31	37	45
7	11	14	15	23	38	7	12	24	32	38	46	7	13	23	30	37	45	8	13	23	30	37	46	8	13	22	29	36	46
35	37	39	46	48	52	7	13	25	32	39	46	8	14	24	32	39	47	8	13	23	30	37	47	7	12	22	28	35	46
3	14	22	47	50	53	5	11	23	30	38	46	5	11	21	28	36	47	5	10	20	27	34	45	5	10	20	27	34	45
12	13	39	41	44	50	5	11	21	26	34	46	5	10	20	25	32	44	5	10	20	25	32	44	5	10	19	24	31	44
3	7	40	42	46	51	4	10	18	23	31	44	4	10	18	24	31	44	4	9	17	22	30	43	4	10	18	23	31	44
1	2	13	31	33	53	4	10	16	22	30	43	4	10	15	20	28	43	4	10	16	21	30	43	4	11	17	23	31	44
1	13	27	33	41	51	5	10	15	19	28	41	5	11	16	20	29	42	4	12	18	22	31	43	4	13	18	24	33	44
5	9	12	15	37	45	5	10	15	19	28	41	5	12	17	21	30	42	5	13	18	23	32	43	4	12	18	23	31	43
3	10	15	19	20	28	5	13	18	22	29	42	5	13	18	24	31	43	4	13	18	24	31	43	4	12	19	25	32	44
6	17	18	19	32	36	5	13	19	25	33	45	5	13	19	24	32	45	4	13	20	25	33	45	4	14	20	26	34	46
14	18	22	28	40	53	4	13	19	25	32	46	4	12	20	26	33	46	4	13	20	26	34	47	4	15	22	27	35	47
8	10	11	14	21	50	3	12	20	26	33	46	3	13	20	26	34	46	3	14	22	27	35	46	3	14	22	29	36	47

VBA: Refactoring a code - Looping trend formula.

montecarlo2012

Well-known Member

Excel Facts

montecarlo2012

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock


						197	128	122	113	127	157	194	163	120	143	132	157	196	138	119	113	117	155	194	140	115	149	123	157
5	15	18	34	37	47	8	18	24	33	40	46	7	18	24	33	40	47	7	18	25	33	40	47	7	18	24	32	39	47
6	23	32	33	42	49	8	19	25	33	40	47	7	18	26	33	40	47	7	18	25	32	39	47	7	17	24	31	38	47
16	30	38	43	51	53	7	17	25	33	40	47	7	17	24	32	39	46	7	16	23	31	37	46	7	17	24	32	37	46
6	26	38	41	42	47	6	16	23	31	37	46	6	15	21	30	36	46	7	15	22	31	36	45	7	15	22	29	35	45
7	16	25	33	43	46	6	14	20	29	35	46	7	14	21	30	36	45	7	14	20	28	35	45	9	16	22	30	36	45
6	12	16	27	30	44	7	14	21	29	35	45	7	14	20	28	34	44	9	16	22	29	35	45	9	16	22	31	36	46
10	19	20	25	29	38	7	14	20	28	34	45	10	16	22	30	36	45	9	16	22	31	37	46	9	16	23	32	37	46
8	23	25	34	49	53	10	16	22	30	36	46	9	16	22	32	38	47	9	16	24	33	38	47	9	15	25	33	39	47
2	5	7	31	37	43	9	15	22	32	36	46	9	15	24	33	37	46	9	14	25	33	38	47	8	13	24	33	37	47
12	18	28	34	40	53	10	16	26	33	37	47	10	15	27	34	38	47	9	14	26	33	38	48	8	14	26	33	38	48
1	12	33	37	40	51	9	15	27	34	38	47	8	14	26	33	37	47	8	13	26	33	38	48	7	13	24	32	38	47
10	17	18	20	30	41	9	14	25	33	37	47	8	14	25	33	37	47	8	13	24	31	37	47	8	13	23	30	36	45
1	5	9	23	24	47	8	13	26	35	38	48	8	13	24	33	38	48	7	13	23	31	36	46	7	13	23	30	36	45
16	22	33	37	40	41	9	14	26	34	40	48	8	13	25	32	38	46	8	14	24	31	37	45	8	14	24	31	37	45
7	11	14	15	23	38	7	12	24	32	38	46	7	13	23	30	37	45	8	13	23	30	37	46	8	13	22	29	36	46
35	37	39	46	48	52	7	13	25	32	39	46	8	14	24	32	39	47	8	13	23	30	37	47	7	12	22	28	35	46
3	14	22	47	50	53	5	11	23	30	38	46	5	11	21	28	36	47	5	10	20	27	34	45	5	10	20	27	34	45
12	13	39	41	44	50	5	11	21	26	34	46	5	10	20	25	32	44	5	10	20	25	32	44	5	10	19	24	31	44
3	7	40	42	46	51	4	10	18	23	31	44	4	10	18	24	31	44	4	9	17	22	30	43	4	10	18	23	31	44
1	2	13	31	33	53	4	10	16	22	30	43	4	10	15	20	28	43	4	10	16	21	30	43	4	11	17	23	31	44
1	13	27	33	41	51	5	10	15	19	28	41	5	11	16	20	29	42	4	12	18	22	31	43	4	13	18	24	33	44
5	9	12	15	37	45	5	10	15	19	28	41	5	12	17	21	30	42	5	13	18	23	32	43	4	12	18	23	31	43
3	10	15	19	20	28	5	13	18	22	29	42	5	13	18	24	31	43	4	13	18	24	31	43	4	12	19	25	32	44
6	17	18	19	32	36	5	13	19	25	33	45	5	13	19	24	32	45	4	13	20	25	33	45	4	14	20	26	34	46
14	18	22	28	40	53	4	13	19	25	32	46	4	12	20	26	33	46	4	13	20	26	34	47	4	15	22	27	35	47
8	10	11	14	21	50	3	12	20	26	33	46	3	13	20	26	34	46	3	14	22	27	35	46	3	14	22	29	36	47