Macro to Multiply Column A and B and Cell D5 and enter resul

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
I have two columns (A & B). I need to have in Column C to product of A & B, i.e. I need A * B in C. But the entire formula is A * B * D5 in C. Also, the length of the lists in A and B varies. I need a macro to do this:

Start on A1
IF AND(A1<>””,B1<>””)
THEN C1 = A1*B1*D5
Goto A2
IF AND(A2<>””,B2<>””)
THEN C2 = A2*B2*D5


How do I do this?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You can do it just like you said. you dont need macro i think. Just put this formula in C1 then copy the next rows:

=IF(AND(A1<>"",B1<>""),A1*B1*D$5)
 
Upvote 0
You don't seem to need a macro, but this
will do if you insist...
Copy the stuff below in place of your
macro. I'm assuming that D5 is a common
factor for all rows????

Sub DoSomeMath()
Dim RowCntr as Long
Dim StaticNumber
StaticNumber = Range("D5").value

For RowCntr = 1 to 65500

If Range("A" & RowCntr)<> "" and _
Range("B" & RowCntr)<> "" Then

If Not IsNumeric(Range("A" & RowCntr) then
msgbox "Invalid Factor - Reference A" & _
RowCntr
ExitSub
End If

If Not IsNumeric(Range("B" & RowCntr) then
msgbox "Invalid Factor - Reference B" & _
RowCntr
ExitSub
End If

Range("C" & RowCntr).value = _
Range("A" & RowCntr).value * _
Range("B" & RowCntr).value * StaticNumber

End If

Next

End Sub
This message was edited by TsTom on 2002-03-19 09:16
This message was edited by TsTom on 2002-03-19 09:17
 
Upvote 0
Excel didn't recognize the ExitSub part.

Tried a simple version of your formula.
Range("D1").Value = Range("B1").Value * Range("C1").Value

Didn't work. Do I need to add a few "s somewhere?

I need a macro because I never know how long the two lists are going to be and this is part of a much bigger macro that actually generates the list by copying and pasting certain cells from several worksheets (worksheets are added and deleted often).
This message was edited by Cosmos75 on 2002-03-19 09:34
 
Upvote 0
Try the following code:

RowCounter = Intersect(ActiveSheet.UsedRange, Columns("A:B")).Rows.Count
For i = 1 To RowCounter
Range("c" & i).Value = Range("a" & i).Value * Range("b" & i).Value * Range("d5").Value
Next i

_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-03-19 09:55
 
Upvote 0
I got it to work BUT when it calculates Column C, it enters a value. In there anyway to change this to enter a formula intead of the value?

THANKS!!
 
Upvote 0
So you don't want the value. You just want the C5 cell to show, for example A5*B5*D5. Is this correct?
 
Upvote 0
If this is correct, try the following code:

RowCounter = Intersect(ActiveSheet.UsedRange, Columns("A:b")).Rows.Count
For i = 1 To RowCounter
If Range("a" & i).Value = "" Or Range("b" & i).Value = "" Then GoTo 1
Range("c" & i).Value = "A" & i & "*" & "B" & i & "*" & "D5"
1 Next i
 
Upvote 0
A very concise example would have been very useful. The following example might be useful.

You do not have to use named ranges.
If your ranges contract or expand, cosider dynamic ranges.

Price Qty 7.00%
5.00 2.00
10.00 3.00
20.00 4.00
Total
128.40

The total is calculated with
=SUMPRODUCT((Price*Qty))*(1+Tax)

which could be

=SUMPRODUCT((a2:A100*B2:B100))*(1+D5)

Ensure that you use references or names cosistent with your data.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top