CODEWITHSUNDEEP
excelvba
Ekko
Ekko

Reputation: 43

Making all cell values in range negative

I am currently trying to do two things.

1) Swap values in two columns. The values are initially listed as Longitude then Latitude. I want Latitude to be listed first and I accomplish this in the first part of the code.

2) Problem Area. The longitudes are published as a positive value, but need to be negative, as a negative value will provide the correct location. I found an answer on

Convert column positive values to negative values VBA

to make negative all values in a range and include the blank cell values in the range. I tried altering the code to match my needs but am getting a Run Time Error '1004' - Method 'Range' of object '_Global' failed.

If anyone could help me out it would be greatly appreciated! 

Public Sub CorrectLongitude()

    Dim tempRange As Variant
    Dim Longitude As Range
    Dim Latitude As Range

    '1) Swap Longitude and Latitude

    Worksheets("Raw_Data").Activate

    Set Longitude = Worksheets("Raw_Data").UsedRange.Columns("AR")
    Set Latitude = Worksheets("Raw_Data").UsedRange.Columns("AS")

    tempRange = Longitude.Cells
    Longitude.Cells.Value = Latitude.Cells.Value
    Latitude.Cells.Value = tempRange

    '2) Make Longitude values negative
    'Variable "Latitude is used as Latitude now contains the longitude values from above

    For Each Latitude In Range(Range("AS"), Range("AS" & Rows.Count).End(xlUp))

        If Not IsEmpty(Latitude.Value) Then If IsNumeric(Latitude.Value) Then Latitude.Value = -Abs(Latitude.Value)

        Next

End Sub

Upvotes: 0

Views: 108

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

You're effectively calling the range Range(Range("AS"),Range("AS1000")). You're missing the first row reference.

For Each Latitude In Range(Range("AS1"), Range("AS" & Rows.Count).End(xlUp))

Edit: A personal suggestion, don't use For each Latitude, since you have Latitude as a defined range elsewhere and this might confuse VBA/yourself. I typically do Dim cel as Range // For each cel in .... If you try and use Latitude.Cells.Value after the For loop, it'll be a much different result than when you called it before.

You might be able to do For each cel in Latitude, assuming that using the UsedRange in Set Latitude = ... includes all the used rows you need.

Upvotes: 1

Related Questions