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