Macro to Multiply Column A and B and Cell D5 and enter resul
Macro to Multiply Column A and B and Cell D5 and enter resul
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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)



  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com