Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Multiply Function in Macro (Excel 2002)

    I've attached an example of some code (compliments of Hans, but I've adapted it to my newest project) and I'd like to add to it... any help is appreciated as usual... below is what I'd like to acheive:

    When column G from the "database" worksheet is copied to the "new" worksheet I'd like to have it multiplied by -1.

    I've left 4 empty spaces after each movement because I'd like the empty rows underneath to be "filled in" with the orignal data that was moved over to the "new" worksheet, however I'd like the first empty space in Column A to add the letters "AP" onto what is in the cell above it (cell A5 has 75PVAL in it, so I'd like cell A6 say 75PVALAP"). I'd like the 2nd empty space to add the letters "NA", the 3rd empty space to add the letters "SA", and the 4th empty space to add the letters "EU". I'd like the account number in column B to simply copy down and fill in the 4 empty spaces below it, as there is no need to "add" anything to it.

    The very last step, after all steps above are done, I'd like to multiply the dollar amount that was transferred over by the % in column D (the % will be there due to a vlookup formula), and rounding 2 places.

    As usual, I appreciate the help! Getting help with the code from this website is helping me learn how to do it myself!

    Thanks,
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiply Function in Macro (Excel 2002)

    Try this The code could be made a bit more compact, but as it is now, it is (or should be) easy to customize the rows if needed.
    <code>
    Sub NonInvenAlloc()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceRow As Long
    Dim lngTargetRow As Long
    Set wshSource = Worksheets("Database")
    Set wshTarget = Worksheets("New")
    lngSourceRow = 3
    lngTargetRow = 5
    Do
    If wshSource.Range("G" & lngSourceRow) <> 0 Then
    ' Original
    wshTarget.Range("A" & lngTargetRow) = wshSource.Range("C" & lngSourceRow)
    wshTarget.Range("B" & lngTargetRow) = wshSource.Range("E" & lngSourceRow)
    ' Set column C to minus the source amount
    wshTarget.Range("C" & lngTargetRow) = -wshSource.Range("G" & lngSourceRow)
    ' Optional: highlight this row
    wshTarget.Range("A" & lngTargetRow & ":C" & lngTargetRow).Interior.ColorIndex = 6
    ' AP
    wshTarget.Range("A" & lngTargetRow + 1) = wshTarget.Range("A" & lngTargetRow) & "AP"
    wshTarget.Range("B" & lngTargetRow + 1) = wshTarget.Range("B" & lngTargetRow)
    wshTarget.Range("C" & lngTargetRow + 1).Formula = "=ROUND(C" & lngTargetRow & _
    "*D" & (lngTargetRow + 1) & ",2)"
    ' NA
    wshTarget.Range("A" & lngTargetRow + 2) = wshTarget.Range("A" & lngTargetRow) & "NA"
    wshTarget.Range("B" & lngTargetRow + 2) = wshTarget.Range("B" & lngTargetRow)
    wshTarget.Range("C" & lngTargetRow + 2).Formula = "=ROUND(C" & lngTargetRow & _
    "*D" & (lngTargetRow + 2) & ",2)"
    ' SA
    wshTarget.Range("A" & lngTargetRow + 3) = wshTarget.Range("A" & lngTargetRow) & "SA"
    wshTarget.Range("B" & lngTargetRow + 3) = wshTarget.Range("B" & lngTargetRow)
    wshTarget.Range("C" & lngTargetRow + 3).Formula = "=ROUND(C" & lngTargetRow & _
    "*D" & (lngTargetRow + 3) & ",2)"
    ' EU
    wshTarget.Range("A" & lngTargetRow + 4) = wshTarget.Range("A" & lngTargetRow) & "EU"
    wshTarget.Range("B" & lngTargetRow + 4) = wshTarget.Range("B" & lngTargetRow)
    wshTarget.Range("C" & lngTargetRow + 4).Formula = "=ROUND(C" & lngTargetRow & _
    "*D" & (lngTargetRow + 4) & ",2)"
    lngTargetRow = lngTargetRow + 5
    End If
    lngSourceRow = lngSourceRow + 1
    Loop Until wshSource.Range("C" & lngSourceRow) = ""
    End Sub
    </code>
    Workbook with modified code is attached.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Multiply Function in Macro (Excel 2002)

    I love it!! Just putting a minus sign in front of the command... I was totally over complicating it... this is great! Adding the color was a good touch as well.
    Thanks Hans!
    Lana

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Multiply Function in Macro (Excel 2002)

    Hi Hans,
    How can I put a multiply by 1 function into the following code line (the format is screwing up my vlookup formulas, so when I multiply my account number by 1 then it fixes the format and the vlookup works... this happens alot as the data is downloaded from our software and fixing the format in Excel ten different ways to Sunday doesn't work... only multiplying by 1 works!??! Who knows why) Anyway here is the line I need to multiply by 1 when it gets copied and pasted to the target worksheet.... I need column E to be multiplied by 1, then place it into column B in the target row.

    wshTarget.Range("B" & lngTargetRow) = wshSource.Range("E" & lngSourceRow)

    Thanks Hans!
    Lana

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiply Function in Macro (Excel 2002)

    You can use

    wshTarget.Range("B" & lngTargetRow) = 1 * wshSource.Range("E" & lngSourceRow)

    or

    wshTarget.Range("B" & lngTargetRow) = Val(wshSource.Range("E" & lngSourceRow))

    The Val function converts the text values to numbers, just like multiplying by 1 does.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Multiply Function in Macro (Excel 2002)

    Ahh.. you make it look so easy... before I asked I tried to do the *1 at the end of the code, and it didn't work. Well... now I know how to multiply AND how to use the VAL function!
    Thanks Hans!
    Lana

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiply Function in Macro (Excel 2002)

    This should work too:

    wshTarget.Range("B" & lngTargetRow) = wshSource.Range("E" & lngSourceRow) * 1

    But not

    wshTarget.Range("B" & lngTargetRow) = wshSource.Range("E" & lngSourceRow * 1)

    for then you only mutiply the row number with 1, not the value of the cell.

Posting Permissions

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