CODEWITHSUNDEEP
excelvbaformulacopy-pasteautofill
Rachel Mears
Rachel Mears

Reputation: 27

Run formula for changing values from positive to negative numbers and then auto-fill down to end of data

Every Monday i run a report of credit memos from our vendor for us to take. The list length changes every time i run this report. The report gives me the dollar amounts as positive numbers even though they are credit memos and should be a negative. After formatting the sheet, my values fall into Columns G,H,I,J. I need to change these to a negative value. I have been manually doing it and would like to use a macro to help me through this part. In columns L-O i would use the following: In L2 = G2*-1 ; M2 = H2*-1; N2=I2*-1; O2=J2*-1. Then i would autofill down the bottom of data. Copy it, paste values over my columns G-J and then delete my formula sections L-O. What is the best macro to use for this?

Record Macro stops at the bottom row of current list but then the next monday, it will only stop at that same row (for example 495) when my new data may go to row 721. 

ActiveCell.Offset(1, 11).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*-1"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*-1"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*-1"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*-1"
ActiveCell.Offset(0, -3).Range("A1:D1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:D1450")
ActiveCell.Range("A1:D1450").Select
Selection.Copy
ActiveCell.Offset(0, -5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveCell.Offset(0, 5).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, -5).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Comma"

Upvotes: 0

Views: 185

Answers (2)

Salim Hasbaya
Salim Hasbaya

Reputation: 85

Try this macro

Option Explicit

Sub Multiplication_by_number()
Dim my_num#: my_num = -1 '  You can Change this number
[e1] = my_num ' You can Change this Location to any Other range
Dim My_rg As Range
[e1].Copy
Set My_rg = Range("A1:C8") 'choose here your data
My_rg.PasteSpecial , Operation:=xlMultiply
Application.CutCopyMode = False
My_rg.Cells(1).Select
End Sub

Upvotes: 0

pnuts
pnuts

Reputation: 59475

For someone with no pressing need for VBA, the easy way is to enter -1 in a spare cell, copy it, select the relevant range and Paste Special Multiply.

Upvotes: 0

Related Questions