Create Formula: Multiply 1 range with value of another range same size

[Uc Thang]

Hi all Master of VBA

I hope you can help me for the issue below. I tried several ways in my ability but it still doesn't resolve
I have two ranges (1 row and unknown number of column) same size. We can call them is range1 & range2.
For example: Range1 = sheets(1).Range(Cells(1,1),Cells(1,Last_Column)) & Range2 = Sheets(1).Range(Cells(2,1),Cells(2,Last_Column))
I want to create a formula in sheets(1).Cells(1,Last_Column + 1) = Cell(1)Range1(refer address not value)*Cell(1)Range2(value) +...+CellRange1(refer address not value)*CellRange2(value)
For the picture attached, (https://imgur.com/1BrY6Ks) I have 5 columns but it is not always 5 like this so I used Last_Column to count
The number which appear on the formula in cell F1 come from range2 (A2:E2)

Hope to get the solution soon
Thank you so much

 

[jorismoerings]

Hi,

Try this:

Sub Macro1()
'
' Macro1 Macro
'
Dim Last_column As Integer
Dim range1, range2 As Range

   Last_column = Sheets(1).Cells(1, Sheets(1).Columns.Count).End(xlToLeft).Column
   Set range1 = Sheets(1).Range(Cells(1, 1), Cells(1, Last_column))
   Set range2 = Sheets(1).Range(Cells(2, 1), Cells(2, Last_column))
     
   Sheets(1).Cells(1, Last_column + 1).Formula = "=SUMPRODUCT(" & range1.Address() & "," & range2.Address() & ")"
    
End Sub

 

[Uc Thang]

Hi jorismoerings
Thank you so much for spending time for me. But in fact, I need to delete row 2 for secret information. So my result in the cell E1 will come to error. Therefore, I want to convert range2 into value and the formula need to be exactly same with what I show on the attached picture. Please help me.

 

[jorismoerings]

Hi,

I don't know what the value of "secret information" is as your picture shows a formula in which this info is visible. hence deleting the line will not result in keep the calculation secret.
Do you want a formula or do you want to show the result of the calculation as a value in the cell?

 

[Uc Thang]

Hi jorismoering
For what I showed you in the picture is just example to know that it is possible to make my issue being solved. In my ability I am not sure what I want can happen with VBA. The range2 is the Factor. It come from a calculation of secret formula of my boss. It isn't same sheet same file with range1. I also know that we can copy value of range2 to position as the picture and it will work with your code but my clients are familiar with form sheet just have range1. Normally I must manually type the formula (same with cell F1 in the picture) then send to a lot of clients (each client has different factor). This take me much time so I find the way to auto input the formula, but I cannot. Hope you can help me for this issue.