Sign placement for negative number I recently discovered that I can move the sig

Question

Sign placement for negative number

I recently discovered that I can move the sign of a negative number from after the number to before it by using DATA | TEST TO COLUMNS | ADVANCED. I created a macro from doing this but the macro always places the result in the same cell "E20". I want to place the result in the same cell that I selected to perform the macro on. I figure the macro need to be modified, here it is

Sub Macro1()
'
' Macro1 Macro
'
Selection.TextToColumns Destination:=Range("E20"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Of course if you know a better way to move the sign from after to before a negative number, I would welcome that.

Explanation / Answer

> ...   I want to place the result in the same cell that I selected

Hi. Your destination is hard-coded.

Here is one way, where the destination is only listed once... at the beginning.   Here, we'll use text values in A1:A2.

Sub Demo()
    [A1] = "123.456-"
    [A2] = "2.34-"
    [A1:A2].Select
  
    With Selection
        .TextToColumns _
        Destination:=.Cells, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    End With
  
End Sub

Or, just don't select...ie

    [A1] = "123.456-"
    [A2] = "2.34-"
    
    With Range("A1:A2")

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.