Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
brain fart, need help
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...?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
brain fart, need 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: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 -- Aren't you supposed to be somewhere else? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
brain fart, need help
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...? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Brain fart in simple subtraction | New Users to Excel | |||
Brain Teaser | Excel Discussion (Misc queries) | |||
Use Your Brain | Excel Programming | |||
Use Your Brain | Excel Programming | |||
Very basic question... I MUST be having a brain fart.... | Excel Programming |