ITERATING A SINGLE CELL!

[jay dean]

What code is used to iterate a single cell? Example: If I wanted to
count the number of characters written in a particular cell.

Any help would be appreciated. Thanks.
Jay Dean

 

[Frank Kabel]

Hi
1. VBA:
msgbox len(range("A1").value)

2. Worksheet formula
=LEN(A1)

 

[jay dean]

Hi-
I wanted the vba code to iterate through one cell. Yes, the len
formula works for counting the elements in a cell (**as an example**),
but what if I wanted to say loop through one single cell to add to or
replace or remove some of its characters?
I just need to know how to programmatically iterate through the
characters in a cell (if it is at all possible). Thanks.

Emmanuel

 

[Frank Kabel]

Hi
try

sub foo()
dim svalue
dim i as integer

svalue=activesheet.range("A1").value
for i = 1 to len(svalue)
msgbox mid(svalue,i,1)
next
end sub

 

[Guest]

Start on the cell you want to change. I have not tested it completely, but it
comes close. Little late maybe I will have a chance to look at it again
tomorrow. Hope it helps.

Sub Macro1()
y = Len(ActiveCell.Value)
AllCharacters = ActiveCell.Value
ReturnAddress = ActiveCell.Address
z = 1
Do Until z = y + 1
ReplaceString = Mid(AllCharacters, z, 1)
Dim Msg, Style, Title, Response
Msg = "Keep this character " & ReplaceString
Style = vbYesNo
Title = "Keep Character?"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
' Do nothing
Stop
Else
' Change or delete character
FrontString = Left(AllCharacters, z - 1)
FrontMid = Mid(AllCharacters, z, y - z)
BackString = Right(AllCharacters, y - z)
Dim Message1, Title1, Default1, MyValue1
Message1 = "Please enter the replacement value"
Title1 = "Replacement value"
Default1 = " "
MyValue1 = InputBox(Message1, Title1, Default1)
AllCharacters = FrontString & MyValue1 & BackString
Stop
End If
z = z + 1
Loop
ActiveCell.Value = AllCharacters
End Sub

 

[jay dean]

Thanks Frank !
Thanks David !

Jay Dean