Dynamic range thru inputbox

[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

 

[Gary''s Student]

Dim r As Range
Set r = Application.InputBox(Prompt:="Enter area containing search words",
Type:=8)
For Each x In r

Then the user can either type the range or use the mouse.

 

[Jan Kronsell]

It returns no errors, but nothing is written in the B-column of sheet 2.
Is it because the search words are in another sheet?

Jan

 

[Jan Kronsell]

I have tried copying the search word range to the same sheet as thr strings,
and then i works perfectly. Unfortunatley that is not an option in the
actuel sheet, where the sub wil be used.

Jan