J
Jan Kronsell
I have the folowwing code, that works perfectly ok
Sub FindOrd()
Dim varX As String
Dim varY As String
Dim FindOrd As String
FindOrd = ""
For Each c In Selection.Cells
FindOrd = ""
For Each x In Sheets(1).Range("A1:A6").Cells
varX = InStr(1, c.Value, x.Value)
If varX <> 0 Then
varY = InStr(varX, c.Value, " ")
If varY = 0 Then
FindOrd = FindOrd & Mid(c.Value, varX, Len(c.Value))
& ", "
Else
FindOrd = FindOrd & Mid(c.Value, varX, varY - varX)
& ", "
End If
End If
Next x
c.Offset(0, 1).Value = FindOrd
Next c
End Sub
The code look in sime text strings in sheet2, column A for some words,
defined i Sheet1, column A, and if found, lists the words, that are found in
a specifik string i column B in Sheet2.
This could be the string in Sheet2 A1. "John and Patricia went for a walk"
In Sheet1 I have the words listed in cells in A-column: Anna, John, Patrica,
Robert
and the code will return John, Patricia, in column B in sheet 2.
But I like to improve the code, so Range("A1:A6") is not hard coded into the
sub, but keyed in or selected via an input box.
I have tried changing the line to
For Each x In Sheets(1).Range(sgrd).Cells
and then adding
Dim sgrd As string
sgrd = Inputbox("Type area containing search words")
It gives no error, but absolutely nothing happens either. If I declare sgrd
as Range it returns a runtime error 91 Object variable or With block
variable not set.
I have also tried using the Input Box method in stead like
Application.InputBox("Choose range with searc words", "Word selection", , ,
, , , 64)
But that doesn't work either.
Any ideas?
Jan
Sub FindOrd()
Dim varX As String
Dim varY As String
Dim FindOrd As String
FindOrd = ""
For Each c In Selection.Cells
FindOrd = ""
For Each x In Sheets(1).Range("A1:A6").Cells
varX = InStr(1, c.Value, x.Value)
If varX <> 0 Then
varY = InStr(varX, c.Value, " ")
If varY = 0 Then
FindOrd = FindOrd & Mid(c.Value, varX, Len(c.Value))
& ", "
Else
FindOrd = FindOrd & Mid(c.Value, varX, varY - varX)
& ", "
End If
End If
Next x
c.Offset(0, 1).Value = FindOrd
Next c
End Sub
The code look in sime text strings in sheet2, column A for some words,
defined i Sheet1, column A, and if found, lists the words, that are found in
a specifik string i column B in Sheet2.
This could be the string in Sheet2 A1. "John and Patricia went for a walk"
In Sheet1 I have the words listed in cells in A-column: Anna, John, Patrica,
Robert
and the code will return John, Patricia, in column B in sheet 2.
But I like to improve the code, so Range("A1:A6") is not hard coded into the
sub, but keyed in or selected via an input box.
I have tried changing the line to
For Each x In Sheets(1).Range(sgrd).Cells
and then adding
Dim sgrd As string
sgrd = Inputbox("Type area containing search words")
It gives no error, but absolutely nothing happens either. If I declare sgrd
as Range it returns a runtime error 91 Object variable or With block
variable not set.
I have also tried using the Input Box method in stead like
Application.InputBox("Choose range with searc words", "Word selection", , ,
, , , 64)
But that doesn't work either.
Any ideas?
Jan