CODEWITHSUNDEEP
excelvba
r_j
r_j

Reputation: 1368

excel macro : Change cell value dependant on original cell value

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.

Upvotes: 0

Views: 1228

Answers (1)

Takedasama
Takedasama

Reputation: 387

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

Upvotes: 1

Related Questions