Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ITERATING A SINGLE CELL!
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ITERATING A SINGLE CELL!
Hi
1. VBA: msgbox len(range("A1").value) 2. Worksheet formula =LEN(A1) -- Regards Frank Kabel Frankfurt, Germany jay dean wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ITERATING A SINGLE CELL!
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ITERATING A SINGLE CELL!
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 -- Regards Frank Kabel Frankfurt, Germany jay dean wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ITERATING A SINGLE CELL!
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" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ITERATING A SINGLE CELL!
Thanks Frank ! Thanks David ! Jay Dean *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From single cell variables to a single column serie | New Users to Excel | |||
Iterating through string in a cell | Excel Discussion (Misc queries) | |||
iterating checkboxes | Excel Discussion (Misc queries) | |||
Iterating Thru Cells | Excel Programming | |||
iterating through all properties of an object | Excel Programming |