CODEWITHSUNDEEP
vbaexcel
Selrac
Selrac

Reputation: 2293

VBA change negative range to positive

I'm trying to change the negative values to positive based on a dynamic range. By dynamic I mean that the row number might change.

For example

rowStart = 10
rowEnd   = 20

From these I need to change the values in this range to positive. I'm trying the following but I'm getting an error:

Range("K" & rowStart & ":K" & rowEnd).Value = _
    Abs(Range("K" & rowStart & ":K" & rowEnd).Value)

The error I get is: Run-time error '13': Type mismatch

Any ideas on how to make this work?

Upvotes: 1

Views: 6827

Answers (5)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

Place in an array and loop over the items (might be faster than updating the range if it's a big range):

Sub Test()

    Dim rowStart As Long, rowEnd As Long
    Dim rRange As Range
    Dim vRange() As Variant
    Dim vItem As Variant
    Dim R As Long

    rowStart = 10
    rowEnd = 20

    With Sheet1
        'Define the range.
        Set rRange = .Range(.Cells(rowStart, 1), .Cells(rowEnd, 1))
    End With
    vRange = rRange

    'Loop through the values - consider R to mean Row.
    For R = 1 To UBound(vRange, 1)
        vRange(R, 1) = Abs(vRange(R, 1))
    Next R

    rRange.Value = vRange

End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

You can't do it in whole that way.

Either Loop:

For i = rowStart to rowEnd
    ActiveSheet.Range("K" & i).Value = Abs(ActiveSheet.Range("K" & i).Value)
Next i

Or you can set it using Evaluate:

ActiveSheet.Range("K" & rowStart & ":K" & rowEnd).Value = Evaluate("INDEX(ABS(" & Range("K" & rowStart & ":K" & rowEnd).Address & "),)")

Upvotes: 6

user4039065
user4039065

Reputation:

Does your number format allow for simply removing the negative signs (i.e. hyphens)?

Dim rowStart As Long, rowEnd As Long
rowStart = 10
rowEnd = 20
With ActiveSheet
    .Range("K" & rowStart & ":K" & rowEnd).Replace What:="-", Replacement:="", LookAt:=xlPart
End With

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96791

You must loop over the items:

Sub ksdjfh()
    Dim r As Range

    rowStart = 10
    rowEnd = 20

    For Each r In Range("K" & rowStart & ":K" & rowEnd)
        r.Value = Abs(r.Value)
    Next r
End Sub

Upvotes: 4

CLR
CLR

Reputation: 12289

Try this:

rowStart = 10
rowEnd = 20
For Each myCell In Range("K" & rowStart & ":K" & rowEnd)
    myCell.Value = Abs(myCell.Value)
Next

Upvotes: 3

Related Questions