Macro to change sign on text file imported to Excel
 

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.

Macro to change sign on text file imported to Excel
 

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

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


"BC" wrote:

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.

Macro to change sign on text file imported to Excel
 

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.Formul aR1C1,
lDash, 1, "")
rCell.FormulaR1C1 = "-" & sValue
End If
Next rCell

HTH

"BC" wrote:

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.

Macro to change sign on text file imported to Excel
 

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).

"Joel" wrote:

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

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


"BC" wrote:

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.

Macro to change sign on text file imported to Excel
 

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

"XP" wrote:


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.Formul aR1C1,
lDash, 1, "")
rCell.FormulaR1C1 = "-" & sValue
End If
Next rCell

HTH

"BC" wrote:

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.