When recording a macro, i want to change the current value of a cell to a new value dependant on the original value. not set it to a "recorded" value.
Example : value in cell needs to change from .1234 (text) to 0,1234 (number). And .56789 to 0,56789
My work method is:
record macro
"F2" : to change value,
"home" : go to beginning,
"del",
"del",
"0",
",",
"return",
stop record macro
but when i use the macro on other fields, the value is changed to 0,1234 even when original value is .5678 for example.
This can be done in VBA but the most simple solution (if all your values begin with ".") is to use the built in "Text to Columns" Excel option (by simply selecting your entire column then ALT+A, +E, +F). If this has to be done with VBA please tell. Hope this alone helps.
Edit I've wrote this code to solve your problem (based on the rule of the cell values are starting with "." and then switching to numbers is made by adding a "0" to the initial text). Then "number" format is picked up by Excel as implicit format.
Sub ChangeFormat()
For i = 1 To 2 'This is the row index (it's now set to the 1st 2 rows.)
For j = 1 To 2 'This is the column Index (i.e. column 2 is B, 1 is A, etc.) (it's now set to the A and B columns)
If Left(ActiveSheet.Cells(i, j), 1) = "." Then
ActiveSheet.Cells(i, j).Value = "0" & ActiveSheet.Cells(i, j).Value
End If
Next j
Next i
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments