change cell value by using factors from another cell

[ThomasOES]

Hello
I would like to place data into a range D14:G19 and multiply each cell by another cell value and have result appear in same cell.

I would like for this to work

Range("D14:G19").Select
For Each cell In Selection
cell = cell * Range("C14").Value
Next

The cells should start as beginning number then are multiplied by Range("C14") and cells end as product.

Thanks
Tom

 

[Joe4]

You can do that pretty easily without a loop, using the Copy -> Paste Special -> Multiply option.
In VBA code, it would look like:

    Range("C14").Copy
    Range("D14:G19").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

 

[ThomasOES]

Thanks Joe
I need to loop because sometimes the last column in the range has less number of rows with data. To copy and paste over the range would produce cells with zeros. Also I sometimes add a constant from cell D14.
Tom

 

[Joe4]

I need to loop because sometimes the last column in the range has less number of rows with data. To copy and paste over the range would produce cells with zeros.

You still do not need to loop. You can most likely use VBA to find the last row/column that you need.
If you can explain the logic for determining exactly where to end, we can probably write code to do it.

Also I sometimes add a constant from cell D14.

I am not sure what you are asking us to do, in this case.
If the cell you are multiplying by may change, how can we determine which cell to use?
Do you want a prompt asking the user? Or, do you want it to use the ActiveCell?
Please let us know what you want to happen.

 

[ThomasOES]

You still do not need to loop. You can most likely use VBA to find the last row/column that you need.
If you can explain the logic for determining exactly where to end, we can probably write code to do it.


I am not sure what you are asking us to do, in this case.
If the cell you are multiplying by may change, how can we determine which cell to use?
Do you want a prompt asking the user? Or, do you want it to use the ActiveCell?
Please let us know what you want to happen.

Sorry for the confusion. I want to apply the factors from cells A10 and B10 to each cell in range B2:E7. For instance B2 would be itself multiplied by A10 then B10 added. Same for all the cells in range B2:E7. Except for E3:E7 since no data.



[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Ca[/TD]
[TD="bgcolor: transparent"]R-1[/TD]
[TD="bgcolor: transparent"]R-2[/TD]
[TD="bgcolor: transparent"]R-3[/TD]
[TD="bgcolor: transparent"]R-4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]MON[/TD]
[TD="bgcolor: transparent"]0.0019[/TD]
[TD="bgcolor: transparent"]0.0018[/TD]
[TD="bgcolor: transparent"]0.0024[/TD]
[TD="bgcolor: transparent"]0.0024[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]CAL 1[/TD]
[TD="bgcolor: transparent"]0.0017[/TD]
[TD="bgcolor: transparent"]0.0017[/TD]
[TD="bgcolor: transparent"]0.0016[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]SAMP 1[/TD]
[TD="bgcolor: transparent"]0.003[/TD]
[TD="bgcolor: transparent"]0.0029[/TD]
[TD="bgcolor: transparent"]0.0027[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]CAL 2[/TD]
[TD="bgcolor: transparent"]0.0047[/TD]
[TD="bgcolor: transparent"]0.0044[/TD]
[TD="bgcolor: transparent"]0.0035[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]SAMP 2[/TD]
[TD="bgcolor: transparent"]0.004[/TD]
[TD="bgcolor: transparent"]0.0038[/TD]
[TD="bgcolor: transparent"]0.0047[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]CAL 3[/TD]
[TD="bgcolor: transparent"]0.0062[/TD]
[TD="bgcolor: transparent"]0.0049[/TD]
[TD="bgcolor: transparent"]0.0062[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] Const[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent, align: right"]0.000035[/TD]
[TD="bgcolor: transparent"] 0.001775[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]