VBA Product of Arrays

rmCalgary

New Member
Joined
Jun 27, 2016
Messages
4
I have a code that is taking vertical data from one sheet and scheduling it out based on an "OnStream" date that is predefined. It returns a row of data for each component type. The next thing I need to do is multiply insert a function that multiplies each of those rows by a set row from of prices.

So the Gas row needs to be multiplied by row 6, component C2 multiplied by row 7, component C3 by row 8 etc. Then returned below all of the component rows.

I would really appreciate the help on this matter.



Code:
Sub MoveComponents(TCDesc, OnStream, WellCnt)
    'paste long desc in column A, TCDesc in B, Component type in C and well # in D
    
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 4, 0).Value = TCDesc & "_Gas_" & WellCnt
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 4, 1).Value = TCDesc
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 4, 2).Value = "Gas"
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 4, 3).Value = WellCnt
    Sheets(TCDesc).Range("E42:E741").Copy
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 4, OnStream + 3).PasteSpecial Paste:=xlValues, Transpose:=True
    
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 3, 0).Value = TCDesc & "_C2_" & WellCnt
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 3, 1).Value = TCDesc
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 3, 2).Value = "C2"
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 3, 3).Value = WellCnt
    Sheets(TCDesc).Range("G42:G741").Copy
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 3, OnStream + 3).PasteSpecial Paste:=xlValues, Transpose:=True


    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 2, 0).Value = TCDesc & "_C3_" & WellCnt
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 2, 1).Value = TCDesc
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 2, 2).Value = "C3"
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 2, 3).Value = WellCnt
    Sheets(TCDesc).Range("H42:H741").Copy
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 2, OnStream + 3).PasteSpecial Paste:=xlValues, Transpose:=True
    
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 1, 0).Value = TCDesc & "_C4_" & WellCnt
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 1, 1).Value = TCDesc
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 1, 2).Value = "C4"
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 1, 3).Value = WellCnt
    Sheets(TCDesc).Range("I42:I741").Copy
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 - 1, OnStream + 3).PasteSpecial Paste:=xlValues, Transpose:=True
    
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6, 0).Value = TCDesc & "_C5_" & WellCnt
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6, 1).Value = TCDesc
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6, 2).Value = "C5"
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6, 3).Value = WellCnt
    Sheets(TCDesc).Range("J42:J741").Copy
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6, OnStream + 3).PasteSpecial Paste:=xlValues, Transpose:=True
    
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 + 1, 0).Value = TCDesc & "_Oil_" & WellCnt
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 + 1, 1).Value = TCDesc
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 + 1, 2).Value = "Oil"
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 + 1, 3).Value = WellCnt
    Sheets(TCDesc).Range("Q42:Q741").Copy
    Sheets("PUD_Prod").Range("A152").Offset(WellCnt * 6 + 1, OnStream + 3).PasteSpecial Paste:=xlValues, Transpose:=True
    
    Sheets ("PUD_Prod")
End Sub
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
This is unclear:
So the Gas row needs to be multiplied by row 6, component C2 multiplied by row 7, component C3 by row 8 etc. Then returned below all of the component rows.

Please use one of the tools mentioned in my sig to post a portion of you worksheets involved in the quoted section.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,947
Messages
5,621,772
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top