将列正值转换为负值VBA

I know this has been asked several times but I'm quite confused on how to put the negative values for my column L:L in a loop. I can't get it to work. I've tried everything I researched. I'd appreciate any help.

我知道这已被问过好几次,但是我很困惑如何把我的L:L列的负值放在一个循环中。我无法让它发挥作用。我已经尝试了所有研究过的东西。我很感激任何帮助。

 Option Explicit
Sub Importpaymentsales()
    Dim fpath As Variant
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim Text As String
    On Error GoTo terminatemsg

    Set wb = Excel.ActiveWorkbook
    Set ws = Excel.ActiveSheet

    fpath = Application.GetOpenFilename(Filefilter:="text Files(*.txt; *.txt), *.txt; *.txt", Title:="Open Prepayment Sales Report")

    If fpath = False Then Exit Sub

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Text = getTextfileData(fpath)
    If Len(Text) Then
        ProcessData Text
        AdjustDates
    Else
        MsgBox fpath & " is empty", vbInformation, "Import Cancelled"
        Exit Sub

    End If
    ws.Range("J:L").Value = ws.Range("J:L").Value
    ws.Range("J:L").numberformat = "#,##0.00"


    ws.Range("O:Q").Value = ws.Range("O:Q").Value
    ws.Range("O:Q").numberformat = "#,##0.00"

    Columns.EntireColumn.AutoFit
    Sheets(1).Move Before:=wb.Sheets(1)

terminatemsg:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    If Err.Number <> 0 Then MsgBox Err.Number & " " & Err.Description

End Sub

Sub ProcessData(Text As String)
    Dim x As Long, y As Long, z As Long
    Dim data, vLine

    data = Split(Text, vbCrLf)
    x = 2
    Range("A1:R1").Value = Array("Supplier Name", "Supplier Number", "Inv Curr Code CurCode", "Payment CurCode", "Invoice Type", "Invoice Number", "Voucher Number", "Invoice Date", "GL Date", "Invoice Amount", "Withheld Amount", "Amount Remaining", "Description", "Account Number", "Invoice in USD", "Withheld in USD", "Amt in USD", "User Name")

    For y = 0 To UBound(data)
        If InStr(data(y), "|") Then
            vLine = Split(data(y), "|")
            If Not Trim(vLine(0)) = "Supplier" Then

                For z = 0 To UBound(vLine)
                    vLine(z) = Trim(vLine(z))

                    If vLine(z) Like "*.*.*.*.*.*.*.*.*.*.*.*.*.*.*" Then vLine(z) = Left(vLine(z), InStr(vLine(z), ".") + 2)

                Next
                Cells(x, 1).Resize(1, UBound(vLine) + 1).Value = vLine
                x = x + 1
                'Range("L2:L").Value = Range("L2:L").Value * (-1)
                Range("L2:L").Value = Abs(rng.Offset(teller - 1, -2).Value) * -1
            End If

        End If
    Next

End Sub

1 个解决方案

#1

Try this:

  Dim r As Range
  For Each r In Range(Range("L2"), Range("L2").End(xlDown))
    If IsNumeric(r.Value) Then r.Value = -Abs(r.Value)
  Next

ps: I suppose you don't have blank cells in-between in column L, if you do then a slight modification is needed.

ps:我想你在L列之间没有空白单元格,如果你这样做,那么需要稍作修改。

Here it is:

这里是:

Dim r As Range
For Each r In Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
    If Not IsEmpty(r.Value) Then If IsNumeric(r.Value) Then r.Value = -Abs(r.Value)
  Next

#1