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
varesourcesAsked:
Who is Participating?
 
SiddharthRoutCommented:
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

Open in new window


Sid
0
 
SiddharthRoutCommented:
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

Open in new window


Sid
0
 
Saqib Husain, SyedEngineerCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dlmilleCommented:
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

Open in new window


See attached.

Enjoy!

Dave
Multiply-two-cells-r1.xls
0
 
varesourcesAuthor 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
 
SiddharthRoutCommented:
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
 
dlmilleCommented:
Try mine :)  a bit late, but tested with sample spreadsheet attached

Dave
0
 
varesourcesAuthor Commented:
Sid yours worked just fine.

Dave I'm in a hurry and didn't get a chance to try it buy I will award you some points for the effort. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.