# Excel - Multiply two cells go to next row?

I have over a thousand rows in an excel sheet. Column w needs to multiply by column v and place the answer in column y. Repeat until there is no more data in v or w. I know this is going to be a simple answer but I can't seem to find it. Thanks!

Oh, and I need it in a macro.
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I would suggest that you do this via a formula but if you want to do it via macro then check this out.

``````Sub Sample()
Dim ws As Worksheet
Dim LastRow As Long, lastRowW As Long, lastRowV As Long, i as Long
Set ws = Sheets("Sheet1")

Application.ScreenUpdating = False

lastRowW = ws.Range("W" & Rows.Count).End(xlUp).Row
lastRowV = ws.Range("V" & Rows.Count).End(xlUp).Row

If lastRowW > lastRowV Or lastRowW = lastRowV Then
LastRow = lastRowW
Else
LastRow = lastRowV
End If

For i = 1 To LastRow
ws.Range("Y" & i).Value = ws.Range("W" & i).Value * ws.Range("V" & i).Value
Next i

Application.ScreenUpdating = True

MsgBox "Done"
End Sub
``````

Sid
0
EngineerCommented:
In cell W1 type

=v1*w1

and then press enter

select cell W1 again

There is a tiny square at the bottom right corner of the selected cell

drag this cell as far down as necessary

Saqib
0
Commented:
Here you go:

``````Sub multWbyVtoY()
Dim wRng As Range
Dim vRng As Range
Dim yRng As Range
Dim myRng As Range
Dim wRows As Long, vRows As Long
Dim maxRows As Long

wRows = Range("W" & Rows.Count).End(xlUp).Row
vRows = Range("V" & Rows.Count).End(xlUp).Row

If wRows >= vRows Then 'ensure we capture all the data in the column, in case some data is omitted on one of the columns (assuming zero for empty cell)
maxRows = wRows
Else
maxRows = vRows
End If

Set wRng = Range("W1", Range("W" & maxRows))
Set vRng = Range("V1", Range("V" & maxRows))
Set yRng = Range("Y1", Range("Y" & maxRows))

'now do the math

For Each myRng In yRng
If Range("W" & myRng.Row).Value > 0 And Range("V" & myRng.Row).Value > 0 Then 'otherwise put zero
myRng.Value = Range("W" & myRng.Row).Value * Range("V" & myRng.Row).Value
ElseIf Range("W" & myRng.Row).Value > 0 Or Range("V" & myRng.Row).Value > 0 Then
myRng.Value = 0
End If
Next myRng

End Sub
``````

See attached.

Enjoy!

Dave
Multiply-two-cells-r1.xls
0
Author Commented:
Sid,

I get an error that says Run-time error '13':
Type mismatch.

End   Debug  Help

This row is highlighted when I click debug
ws.Range("Y" & i).Value = ws.Range("W" & i).Value * ws.Range("V" & i).Value
0
Commented:
Change

ws.Range("Y" & i).Value = ws.Range("W" & i).Value * ws.Range("V" & i).Value

To

ws.Range("Y" & i).Value = Val(ws.Range("W" & i).Value) * Val(ws.Range("V" & i).Value)

Sid
0
Commented:
Try mine :)  a bit late, but tested with sample spreadsheet attached

Dave
0
Commented:
Also If you have headers in Row 1 then you will have to change the code to

``````    For i = 2 To LastRow
ws.Range("Y" & i).Value = Val(ws.Range("W" & i).Value) * Val(ws.Range("V" & i).Value)
Next i
``````

Sid
0

Experts Exchange Solution brought to you by