SUMPRODUCT

[montecarlo2012]

Hi, after hours of searching about sumproduct in the way I got in the code, realize that nothing help me. Please any suggestions here will be appreciated.
this is the line of my problem :

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

this line give me on sheet 1 the result in the following way:

until here

and between the are one column space

and in this way go until GP in other words this loop create 35 arrays, I would like (" IF THAT IS POSSIBLE"), instead going to the right, would be awesome if I can see the same information on sheet 2 in the way is illustrated in the following image:

for this one I have to write for every line sheet1!I2 and so on, and is 10 work books to do the same,

thank you for reading this.

 

[jasonb75]

this is the line of my problem :

I would say that it's part of the problem, there is most likely more problem in the code that you haven't posted.

 

[montecarlo2012]

jasonb75 thank you for taking time to drop a line for me. Do you think I upload the code, and you can give me some hints, Please.
I respect the other people time you know so that why I ask you first.

 

[jasonb75]

Do you think I upload the code

Can't do much without it, the line that you've posted contradicts the result that it is producing so we would need the rest of the code to figure out why.

Please remember to use the correct tags in order to preserve code formatting.

 

[montecarlo2012]

Because I know the sugestions here about investigate first and ask after, then I show you readings I make before I ask for help.
and here I don't see anything close enough

and

so that line is really advance subject, and I am just intermediate vba

 

[montecarlo2012]

Ok, thanks here it is:

Sub THIRD_ORDER_POLYNOMIAL()
         Dim rngStart As Range, rngData As Range
         Dim Diff1 As Long, Diff2 As Long, NoRows As Long, NoCols As Long, i As Long
         Dim s As String
         
         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
End Sub

 

[jasonb75]

I'm really not seeing how post 5 relates to the question.
Try this on a copy of your file, do not use the original unless you want to lose your data if it goes wrong.

Note that I have not changed the line from post 1, but the line above it.

Sub THIRD_ORDER_POLYNOMIAL()
Dim rngStart As Range, rngData As Range
Dim Diff1 As Long, Diff2 As Long, NoRows As Long, NoCols As Long, i As Long
Dim s As String

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(i - Diff1).Resize(NoCols)
        .Formula = "=SUMPRODUCT(--(" & rngData.Resize(NoRows - i, 1).Address(0, 0) & "=" & .Columns(1).Address(0, 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
End Sub

 

[montecarlo2012]

jasonb75 thank you for the warning, and for your time.
I see what you said

I see the change
what this line did, really it is not the output I was waiting, this line just delete all the arrays and display some data beside my work-data.

 

[montecarlo2012]

you know more than me, that for sure. thank you.

 

[jasonb75]

What I've done is based on this line from your post.

I would like (" IF THAT IS POSSIBLE"), instead going to the right, would be awesome if I can see the same information on sheet 2 in the way is illustrated in the following image

I've changed the destination of the output so that it goes down instead of across, which was what you asked for. If it's not what you want then you need to make your examples clearer.

Start with an XL2BB sample (See link below, screen captures are of no use) showing the source data from sheet1 and the expected output in sheet2, making it clear what information is already in the sheet and where the code should be entering the formulas. A sample doesn't need to include all of your data, just enough to explain the problem.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com