I need to run a loop that replaces the last charachter ("0") of every cell in column d with a 1. Since the data stored in each cell is very long, it is stored as text to prevent scientific notation nonsense. help...?

Matthew Dyer wrote:

> I need to run a loop that replaces the last charachter ("0") of every
> cell in column d with a 1. Since the data stored in each cell is very
> long, it is stored as text to prevent scientific notation nonsense.
> help...?

If it's just the last character, then this will do it:
Dim cell As Range, x As String
For Each cell In Range("D1" & Cells.SpecialCells _
(xlCellTypeLastCell).Row)
x = cell.Value
If Len(x) Then
Mid(x, Len(x), 1) = "1"
cell.Value = "'" & x
End If
Next

There are other ways to do the "Mid" line, such as:
x = Right(x, Len(x) - 1) & "1"
....but the way I did it works just fine.

If it's the last "0" character, then do this:
Dim cell As Range, x As String, n As Long
For Each cell In Range("D1" & Cells.SpecialCells _
(xlCellTypeLastCell).Row)
x = cell.Value
n = InStrRev(x, "0")
If n Then
Mid(x, n, 1) = "1"
cell.Value = "'" & x
End If
Next

--
Aren't you supposed to be somewhere else?

hi,

Sub test()
Dim c As Range
For Each c In Range("D1" & Range("D65536").End(xlUp).Row)
If c <> "" Then Range(c.Address) = Left(c, Len(c) - 1) & 1
Next
End Sub

--
isabelle



Le 2012-08-18 15:11, Matthew Dyer a écrit :
> I need to run a loop that replaces the last charachter ("0") of every cell

in column d with a 1. Since the data stored in each cell is very long, it is stored as text to prevent scientific notation nonsense. help...?
>