Multiply Function in Macro (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote