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

[roipatrick]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]5[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]6[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]8[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[VBA Geek]

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

 

[Rick Rothstein]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]5[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]6[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]8[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]

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..

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?

 

[VBA Geek]

if no formulas, then if using my code simply add

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]

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

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?

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

 

[VBA Geek]

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]

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]

That depends on his real case. At least now he has both scenarios and pick the one that fits best

 

[roipatrick]

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


[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column c[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]1[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]


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]

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.