compute column a and column b and show result in column c

roipatrick

New Member
Joined
Mar 20, 2014
Messages
39
Hi and Good day to all of you,

I want to add 2 cells, lets say for example, take a look on the table below

Column AColumn BColumn C
200024000
100055000
20061200
1008800

<tbody>
</tbody>

As you can see, I want column C to render the product of column a and b, Take note that I want this to be done in macro VBA code and not in formulas.

Thank you in advance to all of you guys,

So new here in VBA as in super new.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Code:
Sub PROD()
    Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=" & _
                                                                    "PRODUCT($A1, $B1)"
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
Hi and Good day to all of you,

I want to add 2 cells, lets say for example, take a look on the table below

Column AColumn BColumn C
200024000
100055000
20061200
1008800

<tbody>
</tbody>

As you can see, I want column C to render the product of column a and b, Take note that I want this to be done in macro VBA code and not in formulas.

So new here in VBA as in super new.
If you are that new in VBA and using this question as a way to learn programming in VBA, then the macro I wrote below will probably not be of much help to you; however, this is how I would do what you asked..
Code:
Sub ColumnCequalsColumnAtimesColumnB()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1:C" & LastRow) = Evaluate(Replace("A1:A#*B1:B#", "#", LastRow))
End Sub
Edit Note
---------------
Unlike VBA Geek, I assumed you did not want a formula in the cells of Column C once the macro had finished running, otherwise, why not simply put the formulas in the cells directly?
 
Last edited:

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
if no formulas, then if using my code simply add :)

Code:
Sub PROD()
    With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=PRODUCT($A1, $B1)"
        .Value = .Value
     End With
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
if no formulas, then if using my code simply add :)
Correct (it was your code, so I figured I would let you post the line of code to add).

if no formulas, then if using my code simply add :)

Code:
Sub PROD()
     With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=PRODUCT($A1, $B1)"
         .Value = .Value
      End With
 End Sub
Question... why did you choose to use the PRODUCT function rather than using straight multiplication?
Code:
Sub PROD()
    With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=A1*B1"
        .Value = .Value
     End With
End Sub
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
I believe it is a bit faster calculating then the *, although it is not noticeable in this case. Also it ignores text and blank cells so it does not return the #VALUE error if by coincidence the user has some text in between or 0 if cells are blank. Also I like it better because you can multiply many numbers easily together, say that Patrick wanted to multiply columns A:E, instead of doing a2*b2*c2*d2*e2, it would have been just product(a2:e2)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
Also it ignores text and blank cells so it does not return the #VALUE error if by coincidence the user has some text in between or 0 if cells are blank.
Using the data posted by the OP, change cell B2 to a non-numeric text value and B3 to a blank cell, then re-run the code you posted in Message #4... are you sure the OP would want the results in cells C2 and C3 instead of the #VALUE! error and 0 results from my code (I am not 100% sure how desirable that 0 from mine would be though)?
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
That depends on his real case. At least now he has both scenarios and pick the one that fits best :)
 

roipatrick

New Member
Joined
Mar 20, 2014
Messages
39
Hi To both of you and thanks a lot for helping me out. Now that you mentioned it I forgot to tell you that this computation in constant.

Lets say


Here's my table


Column AColumn BColumn c
10021000
20011000

<tbody>
</tbody>


Now to be more clear......

If you see 100 * 2 is not 1000 but in this for if column B has a value of 2 then it is similar to 10. So column A which is 100 multiply by column b which is technically 10 while on the second line column b which is 1 is technically 5 so Column A is 200 multiply by column b which is 5 so the answer is 1000.

So here's the table..
1 = 5
2 = 10
3= 15

Like so....Peace out...hope you can help me on this one... So i want the whole column to be like that.

Thanks and Advance thanks hhehehe.:)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
Now to be more clear......

If you see 100 * 2 is not 1000 but in this for if column B has a value of 2 then it is similar to 10. So column A which is 100 multiply by column b which is technically 10 while on the second line column b which is 1 is technically 5 so Column A is 200 multiply by column b which is 5 so the answer is 1000.

So here's the table..
1 = 5
2 = 10
3= 15
Is that table of equivalent values fixed and constant (meaning it will never change, so that it can be hard-coded within the macro itself) or is what you posted only a simplified example and the real table is longer are more variable than shown? If the latter, do you have this table existing on a worksheet somewhere? If so, what is the sheet name and range for the table... if not, then you will need to put the table somewhere and then tell us the sheet name and range where you put it.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,290
Messages
5,449,492
Members
405,567
Latest member
thantzaw2005

This Week's Hot Topics

Top