brain fart, need help

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

Auric__

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

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
 
I

isabelle

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top