Return Negative Value

[MAW]

I have a spreadsheet with rows (and the number of rows will vary all the time) that column A is either a 40 or a 50 and column b is a amount(all positive numbers). If col A.=40 then I need the amount in column B to be the negative of the amount there and if it's 50 then the amount can stay postive. I have pretty new to VBA and am not having much luck getting a macro to work. Is this possibe?

 

[Barrie Davidson]

On 2002-03-26 08:52, MAW wrote:
I have a spreadsheet with rows (and the number of rows will vary all the time) that column A is either a 40 or a 50 and column b is a amount(all positive numbers). If col A.=40 then I need the amount in column B to be the negative of the amount there and if it's 50 then the amount can stay postive. I have pretty new to VBA and am not having much luck getting a macro to work. Is this possibe?

Why not use a formula in an adjacent column (say column C)? Something like:

=IF(A1=40,-B1,B1)

Would this work for you?

 

[MAW]

That is actually what it's doing right now. But this is just a piece of a macro that I have and it really is slowing it down because it is going line by line, if col A. = 40 then it is putting a neg. amount in column C and if it is 50 it puts the pos. amount in column C (for approx 16,000 lines). Then it has to copy/paste special the amounts in Column c and then delete column B. I just thought there might be an easier way.

This is the entire macro (I didn't write it, I inherited it.)I put ****** around the piece I'm questioning.

Thanks!

Sub RevenueUpload()
'
' RevenueUpload Macro
' '

'
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
While Cells(1, 1).Value<> "D"
Range("a1").Select
If ActiveCell.Value Like "H" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
End If
If ActiveCell.Value Like "F" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
End If
Wend
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "Db/Cr"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Amount"
Range("C1").Select
ActiveCell.FormulaR1C1 = "G/L Acct"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tax"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Pft Ctr"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Acct#"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Product"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Industry"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Sales Rep"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Region"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Proj/Alloc"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Customer Name"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Amount"
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
*********************************************
i = 2
While Cells(i, 1).Value<> ""
Cells(i, 1).Select
If ActiveCell.Value Like "40" Then
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=-RC[-1]"
Else
If ActiveCell.Value Like "50" Then
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
End If
End If
i = i + 1
Wend
Columns("C:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
*********************************************
ActiveWorkbook.SaveAs FileName:="\OWASCORevenueTS Trans_Temp_Files" & "All Regions" & "_" & format(Date, "mm-dd-yy") & ".xls"
Cells(1, 1).Select
End Sub
This message was edited by MAW on 2002-03-26 09:32

 

[MAW]

Anybody?