brain fart, need help

[Matthew Dyer]

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

 

[Auric__]

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

 

[isabelle]

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