Results 1 to 4 of 4

Need to multiply my discount amount by -1

Threaded View

  1. #1
    Registered User
    Join Date
    08-22-2021
    Location
    Portugal
    MS-Off Ver
    Excel 2021 Mac
    Posts
    79

    Need to multiply my discount amount by -1

    I am using MS Excel 2021 Mac.
    I am using some VBA code that works just fine.
    I copied the logic that inserts the "Tips" amount for a sales transaction to also insert a new row if there is a discount amount. See block "Insert discount row".
    The discount amount is in column K as a negative number.

    here is my code

    Sub QBLoad1_UpdateQBLoad()
    Dim Rw, Order As String, i As Long
    
    With Sheets("QBLoad")
        For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1
        
           ' Check for gift set
            If .Range("D" & i) = "Gift Set" Then
                .Range("D" & i).Interior.Color = vbRed
                .Range("H" & i).Font.Color = vbRed
                .Range("H" & i).Font.Bold = True
            End If
            
            ' Insert discount row
            If .Range("K" & i) <> 0 Then
                Order = .Range("AD" & i)
                With .Cells(1).CurrentRegion.Columns(30): Rw = Evaluate("=MAX(IF(" & .Address & "=" & Order & ",ROW(" & .Address & ")-MIN(ROW(" & .Address & ")) + 1))"): End With
                .Rows(i).Copy: .Rows(Rw + 1).Insert Shift:=xlDown
                
                With .Range("A" & Rw + 1 & ":AL" & Rw + 1)
                
                    ' Set value Tips
                    Union(.Columns("D:E"), .Columns("G:H")).Value = "Discount"
                    .Columns("G").Font.ColorIndex = 46
                    .Columns("G").Font.Bold = True
                    .Columns("H").Font.ColorIndex = 46
                    .Columns("H").Font.Bold = True
                    
                    ' Set quantity to blank
                    .Columns("F").Value = " "
                    
                    ' Set discount amount
                     Union(.Columns("J"), .Columns("L"), .Columns("AE")).Value = .Parent.Range(("K" & i) * -1)
                    
                    ' Set cell color and make bold
                    .Columns("AE").Font.Color = vbBlue
                    .Columns("AE").Font.Bold = True
                     
                     ' Clear 6 cells from AF
                    .Columns("AF").Resize(, 6).ClearContents
                End With
            End If
            
            ' Insert Tips row
            If .Range("AG" & i) <> "" Then
                Order = .Range("AD" & i)
                With .Cells(1).CurrentRegion.Columns(30): Rw = Evaluate("=MAX(IF(" & .Address & "=" & Order & ",ROW(" & .Address & ")-MIN(ROW(" & .Address & ")) + 1))"): End With
                .Rows(i).Copy: .Rows(Rw + 1).Insert Shift:=xlDown
                
                With .Range("A" & Rw + 1 & ":AL" & Rw + 1)
                
                    ' Set value Tips
                    Union(.Columns("D:E"), .Columns("G:H")).Value = "Tips"
                    .Columns("G").Font.ColorIndex = 46
                    .Columns("G").Font.Bold = True
                    .Columns("H").Font.ColorIndex = 46
                    .Columns("H").Font.Bold = True
                    
                    ' Set quantity to blank
                    .Columns("F").Value = " "
                    
                    ' Set tips amount
                    Union(.Columns("J"), .Columns("L"), .Columns("AE")).Value = .Parent.Range("AG" & i)
                    
                    ' Set cell color and make bold
                    .Columns("AE").Font.Color = vbBlue
                    .Columns("AE").Font.Bold = True
                     
                     ' Clear 6 cells from AF
                    .Columns("AF").Resize(, 6).ClearContents
                End With
            End If
        Next i
        
        ' Save last transaction number to Formula
        i = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("I" & i).Copy Destination:=Sheets("Formulas").Range("I1")
        
        ' Change column width for US date/time
        Columns("AL").ColumnWidth = 16
        
        ' Copy Load data to Save sheet
        ' Clear sheet Load first
        Sheets("Save").Cells.Clear
        
        i = Sheets("QBLoad").Cells(.Rows.Count, "A").End(xlUp).Row
        Sheets("QBLoad").Range("A1" & ":AL" & (i)).Copy
        
        ' Now paste values and formats
        Sheets("Save").Range("A1").PasteSpecial xlPasteValues
        Sheets("Save").Range("A1").PasteSpecial xlPasteFormats
        
        ' Now copy Load sheet to Load Excel
        Dim SquareLoad As Workbook
        Dim SourceSheet As Worksheet
    
        On Error Resume Next
    
        ' Set the source worksheet
        Set SourceSheet = ThisWorkbook.Worksheets("QBLoad")
    
        ' Find the Square-load.xlsx workbook
        Set SquareLoad = Workbooks("Square-load.xlsx")
    
        ' Did we find the workbook?
        If SquareLoad Is Nothing Then
            ' Open it - assume it's in the same folder
            Set SquareLoad = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "Square-load.xlsx")
        
            ' Did we find it there?
            If SquareLoad Is Nothing Then
                ' No - error and exit
                MsgBox "Square-load.xlsx not found in folder", vbCritical
                Exit Sub
            End If
        End If
    
        ' Copy the sheet to be the second sheet of the destination workbook
        SourceSheet.Copy After:=SquareLoad.Sheets(1)
    
        ' Delete the previous "Load" sheet in the destination workbook
        Application.DisplayAlerts = False
        SquareLoad.Worksheets(1).Delete
        Application.DisplayAlerts = True
    
        ' Change the name of the worksheet
        SquareLoad.Worksheets(1).Name = "SalesReceipt"
    
        ' Save the destination book
        SquareLoad.Save
            
        ' Sort by Date and time
        ' Columns.Sort key1:=Columns("A"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, Header:=xlYes
    End With
    
    End Sub
    I get an error on this line where I want to multiply the discount amount by -1.
    Union(.Columns("J"), .Columns("L"), .Columns("AE")).Value = .Parent.Range(("K" & i) * -1)

    The discount amount in the excel has to be a positive number, as it is converted to a negative amount when I import it into Quickbooks.

    What am I doing wrong?
    Last edited by gerdgoebel; 04-14-2024 at 12:09 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to calculate percentage to match old discount amount
    By avk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2024, 04:14 AM
  2. [SOLVED] Calculate the Discount amount i got on VAT invoice
    By HiS2oKa in forum Excel General
    Replies: 9
    Last Post: 10-17-2022, 09:37 PM
  3. [SOLVED] Formulate discount based on total amount
    By jaredxeon in forum Excel General
    Replies: 2
    Last Post: 06-04-2022, 04:38 AM
  4. [SOLVED] Different discount for different products amount
    By AugustsD in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-30-2015, 07:28 AM
  5. [SOLVED] [SOLVED} array of discount multiply to array of amount
    By kirby21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2014, 11:31 PM
  6. How do I discount by % not amount
    By GaryPip in forum Excel General
    Replies: 18
    Last Post: 12-03-2009, 09:02 PM
  7. Discount based on amount?
    By seaniexxx in forum Excel General
    Replies: 2
    Last Post: 02-06-2009, 06:14 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1