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

[Cosmos75]

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?

[smozgur]

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)

[Tom Schreiner]

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 ]

[Cosmos75]

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 ]

[Al Chara]

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 ]

[Cosmos75]

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

[Al Chara]

So you don't want the value. You just want the C5 cell to show, for example A5*B5*D5. Is this correct?

[Al Chara]

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

[Cosmos75]

I do want the value, but I want the formula to be shown in Column C , i.e. =A1*B2*D5

[Dave Patton]

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.