Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Return Negative Value

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anybody?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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