Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
iTunes Library text file imported into Excel | Excel Discussion (Misc queries) | |||
formatting a text file imported into excel | Excel Worksheet Functions | |||
how do I change the text case of imported data in excel? | Excel Discussion (Misc queries) | |||
Parsing imported text file with macro... help! | Excel Programming | |||
if then elseif on text file imported into excel | Excel Programming |