Macro to change sign on text file imported to Excel

[Guest]

I have to import a text file from an application to Excel so I can analyze
the numbers.

After I have imported the file into Excel, I have to change some cells which
show up as 1069.63- (for instance). To change this to a usable value, the
key strokes I would use are Edit[F2], Home, -, Yes/Enter.

When I use the Macro recorder, I get the following.

Sub CHANGE_SIGN()
'
ActiveCell.FormulaR1C1 = "-1069.63"
Range("I83").Select
End Sub

I want to be able to put the cursor on a selected cell anywhere on the page
and run the macro. As I have recorded the macro, it does not work.

Ideally, the macro could have the ability to change multiple cells, or
ranges on the worksheet.

After I had recorded the macro, on the Stop recording bar, I was not able to
change between absolute or relative referencing, if this is an issue.

Any help is appreciated as there are a number of cells to change.

 

[Guest]

There are lots of ways to code this. Lets see how many responses we get

Sub CHANGE_SIGN()
'
ActiveCell = -1 * Val(ActiveCell)
End Sub

 

[Guest]

I like Joel's response; clean approach.

Here is another method that ignores values unless they contain a dash (minus
sign). Using this method, you need to make your selection first, then run.
Watch for line wrapping:

Dim lDash As Long
Dim sValue As String
Dim rCell As Range
For Each rCell In Selection
If InStr(1, rCell.FormulaR1C1, "-", vbTextCompare) <> 0 Then
lDash = InStr(1, rCell.FormulaR1C1, "-", vbTextCompare)
sValue = Application.WorksheetFunction.Replace(rCell.FormulaR1C1,
lDash, 1, "")
rCell.FormulaR1C1 = "-" & sValue
End If
Next rCell

HTH

 

[Guest]

Thanks for the suggestion, but it didn't work for me. When I tried it, the
result would be a negative (OK, that part worked) but the resulting value
changed.

For example, running the macro on a cell that appears as 5,016.41- returned
(5.00).

There are lots of ways to code this. Lets see how many responses we get

Sub CHANGE_SIGN()
'
ActiveCell = -1 * Val(ActiveCell)
End Sub

I have to import a text file from an application to Excel so I can analyze
the numbers.

After I have imported the file into Excel, I have to change some cells which
show up as 1069.63- (for instance). To change this to a usable value, the
key strokes I would use are Edit[F2], Home, -, Yes/Enter.

When I use the Macro recorder, I get the following.

Sub CHANGE_SIGN()
'
ActiveCell.FormulaR1C1 = "-1069.63"
Range("I83").Select
End Sub

I want to be able to put the cursor on a selected cell anywhere on the page
and run the macro. As I have recorded the macro, it does not work.

Ideally, the macro could have the ability to change multiple cells, or
ranges on the worksheet.

After I had recorded the macro, on the Stop recording bar, I was not able to
change between absolute or relative referencing, if this is an issue.

Any help is appreciated as there are a number of cells to change.

 

[Guest]

Thanks XP, that was the result I was hoping for.

I like Joel's response; clean approach.

Here is another method that ignores values unless they contain a dash (minus
sign). Using this method, you need to make your selection first, then run.
Watch for line wrapping:

Dim lDash As Long
Dim sValue As String
Dim rCell As Range
For Each rCell In Selection
If InStr(1, rCell.FormulaR1C1, "-", vbTextCompare) <> 0 Then
lDash = InStr(1, rCell.FormulaR1C1, "-", vbTextCompare)
sValue = Application.WorksheetFunction.Replace(rCell.FormulaR1C1,
lDash, 1, "")
rCell.FormulaR1C1 = "-" & sValue
End If
Next rCell

HTH

I have to import a text file from an application to Excel so I can analyze
the numbers.

After I have imported the file into Excel, I have to change some cells which
show up as 1069.63- (for instance). To change this to a usable value, the
key strokes I would use are Edit[F2], Home, -, Yes/Enter.

When I use the Macro recorder, I get the following.

Sub CHANGE_SIGN()
'
ActiveCell.FormulaR1C1 = "-1069.63"
Range("I83").Select
End Sub

I want to be able to put the cursor on a selected cell anywhere on the page
and run the macro. As I have recorded the macro, it does not work.

Ideally, the macro could have the ability to change multiple cells, or
ranges on the worksheet.

After I had recorded the macro, on the Stop recording bar, I was not able to
change between absolute or relative referencing, if this is an issue.

Any help is appreciated as there are a number of cells to change.