Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Range

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am not a pro at macros, I am learning as I go. My problem is that I want the minus sign to move from the right to the left, and that works fine. The problem is...the range changes in reference to the number of rows column A occupies.

    The minus sign I want to move are in columns L through N and Q through W

    Can someone please help, Thanks


    ColumnARange = Intersect(ActiveSheet.UsedRange, Columns("A")).Address
    Range("K2:m2").Select
    On Error Resume Next
    Dim cel As Range
    Dim myVar As Range
    Set myVar = Selection
    For Each cel In myVar
    If Right((Trim(cel)), 1) = "-" Then
    cel.Value = cel.Value * 1
    End If

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try this...


    Sub test()

    rowx = 2
    For colx = 12 To 14 ' L through to Q
    valx = Cells(rowx, colx).Value
    If Right(valx, 1) = "-" Then
    Cells(rowx, colx).Value = "-" & Left(valx, Len(valx) - 1)
    End If
    Next

    For colx = 17 To 23 ' Q through to W
    valx = Cells(rowx, colx).Value
    If Right(valx, 1) = "-" Then
    Cells(rowx, colx).Value = "-" & Left(valx, Len(valx) - 1)
    End If
    Next

    End Sub

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried it and it did not work. Any ideas??? Thanks

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi Parra. how about this

    Sub test2()
    Dim rng As Range
    For Each rng In Range("Q:W,L:N").SpecialCells(2)
    With rng
    If Right(.Value, 1) = "-" Then .Value = Val(.Value) * -1
    End With
    Next
    End Sub

    [ This Message was edited by: Colo on 2002-04-04 22:33 ]

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
  •