brain fart, need help

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

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...?
 
Matthew said:
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...?
Click to expand...

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:D" & 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:D" & 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
 
hi,

Sub test()
Dim c As Range
For Each c In Range("D1:D" & 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
Click to expand...

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