Multiply Cells in the Same Cell by Adjacent Cell

[jswhip]

I have two columns of data. For each row, I would like to replace the cell in the 2nd column with the product of the two cells in a VBA macro. I've seen examples of accomplishing this with a constant, but not with changing values.

I.E. (before macro runs):
234 10.0
54536 0.2
4 5.0

(after macro runs)
234 2340.0
5456 1091.2
4 20.0

Thoughts????

 

[Eric W]

A VBA macro is possible, but you can do that just as quick without one. Just copy the first column, then right click on the second column, select Paste Special, Paste Special again, then check the Multiply box and click OK.

 

[jswhip]

Thanks, but this needs to be in a macro. There's a lot of processing that goes on after this step.

 

[Eric W]

There must be dozens of ways to do this, depending on what other processing you have in mind. For example:

Sub Macro3()
    
    Columns("A:A").Copy
    Columns("B:B").Select
    Selection.PasteSpecial Operation:=xlMultiply
End Sub

is a VBA version of what I described earlier, created with the help of the Record Macro option.

or this:

Sub MultColB()
Dim i As Long

    For i = 1 To 10
        Cells(i, "B") = Cells(i, "A") * Cells(i, "B")
    Next i

End Sub

where you adjust the parts in red to match your worksheet. Or depending on how your worksheet is laid out, there are ways to find the top and bottom rows.

You just need to be specific on what you need.

 

[Scott Huish]

In the PasteSpecial Method, the Select part is unnecessary:

Sub test()
Range("A:A").Copy
Range("B:B").PasteSpecial , xlPasteSpecialOperationMultiply
Application.CutCopyMode = False
End Sub

 

[Eric W]

Thanks for the clarification, Scott. Sometimes cleaning up from the Record Macro tool takes me an extra step or two.

 

[jswhip]

Thanks - that did the trick. In researching other solutions, they used "evaluate", "value", and "address" within a range with a constant. I couldn't get the syntax to work by referencing a column. Here's what I ended up using:

Sub GetCMBS()

Dim r As Integer

If WorksheetFunction.CountIf(Cells, "N.A.") > 0 Then

Application.OnTime Now() + TimeValue("00:00:02"), "CMBS_DB.GetCMBS"

Exit Sub

End If

LastCellRow = Cells(65536, 2).End(xlUp).Row
Range(Cells(FirstCellRow + 1, 6), Cells(LastCellRow, 6)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

For r = FirstCellRow + 1 To LastCellRow

Cells(r, "F") = Cells(r, "E") * Cells(r, "F")

Next r

End Sub

 

[Eric W]

I'm glad you got it to work. Since FirstCellRow is never defined, it will default to 0, so your loop at the end starts at 1 and goes to LastCellRow.