• Multiply Function in Macro (Excel 2002)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Multiply Function in Macro (Excel 2002)

    Author
    Topic
    #433231

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1018558

      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.

      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

      Workbook with modified code is attached.

      • #1018644

        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

        • #1018680

          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

          • #1018682

            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.

            • #1018691

              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

            • #1018693

              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.

    Viewing 0 reply threads
    Reply To: Multiply Function in Macro (Excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: