Multiply a range of cells by user input in VBA for Excel-CodePudding

I'm trying to write a code that asks for the user input for the number of assemblies required from a bill of materials. Once the user inputs the number, a new worksheet is generated that is supposed to show all the parts used and the number of parts, the number would be the number of existing parts multiplied by the user input. I keep getting mismatch errors when trying to run the code, and I am not sure what I am doing wrong. Is there another way to multiply a range of cells by user input that I am missing?

Dim qty As Integer 'set number of parts/assemblies as qty
Dim intRowNo As Double
Dim intColNo As Double
Dim RowCount As Integer
Dim ColCount As Integer
qty = InputBox("How many assemblies/parts are needed?") 'input box for number of parts/assemblies required
Sheets.Add.Name = "WorkOrder"
Range("A1").Value = "Part Number"
Range("B1").Value = "Part Name"
Range("C1").Value = "BoM Qty."
Range("D1").Value = "QtY."
Range("A2:A8").Value = Worksheets("C63 TOE LINK KIT").Range("A6:A12").Value
Range("B2:B8").Value = Worksheets("C63 TOE LINK KIT").Range("B6:B12").Value
Range("C2:C8").Value = Worksheets("C63 TOE LINK KIT").Range("E6:E12").Value
Range("D2").Activate
intRowNo = ActiveCell.Row
intColNo = ActiveCell.Column
ColCount = 1
For RowCount = 1 To 7


Cells(intRowNo   RowCount, ColCount).Value = qty * Cells(intRowNo   RowCount, ColCount).Value
Next RowCount

CodePudding user response：

Using excel formulas: replace everything starting at

Range("D2").Activate

with

Range("D2:D8").formular1c1 = "=" & QTY & "*RC[-1]"