I cannot understand what you want to do exactly.
A simple workbook might be helpful.
(Also, rng is not defined.)
I would like to extract whole word according to starting string.
I wrote following code, but function "find" is not exist.
Please help to fix it.Code:Sub GetFullName() Dim str As String str = InputBox("Requested String?") Range("A2").Select Range(Selection, Selection.End(xlDown)).Select For Each rng In Selection rng.Offset(0, 1) = Mid(A2, Find(str, A2, 1), Find(" ", A2, Find(str, A2, 1)) - Find(str, A2, 1)) Next rng End Sub
The equivalent to the FIND worksheet function in VBA is InStr. The arguments for this (at least the ones that are usually used) are:
1) start position: set to 1 if looking from the start of the text
2) text to look in: in your case, something like 'rng.Value'
3) the text to find: in your case " ".
As with Wigi, I can't quite understand what you'r trying to achieve, but hopefully this explanation of the InStr will enable you to do it.
By the way, your 'test.xls' file wasn't attached.
Last edited by Batman; August 21st, 2006 at 21:34.
EDIT: wrong post, I understand the purpose now.
And what should be in the column next to it?
Everything from column B, except that what you're looking for? SOT in this case.
Last edited by Wigi; August 21st, 2006 at 21:41.
this works, but I reckon it could be simpler.
I'm not sure how to handle the situation where the SOT is at the end of the line (i.e. no space afterwards anymore)Code:Sub GetFullName() Dim str As String, rng As Range, beginPos As Integer, endPos As Integer str = InputBox("Requested String?") For Each rng In Range("A2", Range("A2").End(xlDown)) beginPos = InStr(1, rng.Value, str) On Error Resume Next endPos = InStr(beginPos, rng.Value, " ") On Error GoTo 0 If endPos = 0 Then endPos = Len(rng.Value) + 1 If InStr(1, rng.Value, str) > 0 Then rng.Offset(0, 1) = Mid(rng.Value, beginPos, endPos - beginPos) End If Next rng End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)