multiplying numbers in two different columns with a loop code

Hey guys,

I work for a machinery company and I import 100 ~ 300+ excels files into 1 single file by using excel power query,

Each excel file contains a number for (Machines) and 3 numbers for (Parts), I need to multiply the above numbers to calculate the total purchase amount.

Please refer to the picture below:
excel 3.png

Each color represents a different excel file(all files are in the same format), the best way I can come up with is to write a code for each excel file:

D3=$B$2*$A3
D4=$B$2*$A4
D5=$B$2*$A5

D8=$B$7*$A8
D9=$B$7*$A9
............etc

There are more than 2000+ lines and it would take too much time to write a code for every single column,

I'm wondering if there is a way to use loop for excel to select the respective columns that need to be multiplied and stop when it reaches the bottom of the worksheet.

I have been looking for a solution for days and still can't figure out a way to do it as I'm new to VBA.

I would really appreciate your help, thanks

Try:

Please Login or Register  to view this content.

You are very welcome and thanks for the rep.