Results 1 to 3 of 3

Thread: Why the row selected is displayed in negative value : Range ().value = Target.row

  1. #1
    New Member
    Join Date
    Sep 2019
    Location
    Morocco
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Why the row selected is displayed in negative value : Range ().value = Target.row

    Hello guys,
    I have just joined this amazing place and I'm still novice in VBA . From the code below, I would like to display rows number of selected cells in cell "A4". The issue is I have the first number displayed in negative value without parenthesis. However I want to display a positive value between parenthesis.
    Is something missing in the code
    Thanks for your great help !
    Code:
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Not Intersect(Target, Range(" D22:I46 ")) Is Nothing Then        Dim Cell As Range        If Target.Count > 1 And Target.Count < 70 Then        For Each Cell In Selection        Range("A4").Value = Range("A4").Value & "(" & Cell.Row - 21 & ")"        Next Cell        Else        Range("A4").Value = "(" & Target.Row - 21 & ")"        End IfElseRange("A4").ClearContentsEnd IfEnd Sub

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Why the row selected is displayed in negative value : Range ().value = Target.row

    The code loops through each cell selected. With the first cell in the loop, it puts something like (1). Excel evaluates that entry to just its' numeric negative (like a currency value).

    Just format A4 as text and that should fix it.

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim Cell  As Range
        Range("A4").ClearContents
        Range("A4").NumberFormat = "@" 'Text format
        If Not Intersect(Target, Range("D22:I46")) Is Nothing Then
            For Each Cell In Intersect(Target, Range("D22:I46"))
                Range("A4").Value = Range("A4").Value & "(" & Cell.Row - 21 & ")"
            Next Cell
        End If
    End Sub
    Last edited by AlphaFrog; Sep 17th, 2019 at 07:56 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Sep 2019
    Location
    Morocco
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why the row selected is displayed in negative value : Range ().value = Target.row

    It worked ! Thanks so much AlphaFrog

Some videos you may like

User Tag List

Tags for this Thread

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
  •