Searching for keywords in vba

[crunchie]

Hi,

I want to search a string for certain keywords and if that search returns a false result i.e. the keyword was not found, then a range of cells are copied and pasted to another sheet.

The data i am using is seperated by "/" e.g.

/vehicle/car/Lexus/suspension/.../... etc

The length of these strings can vary.

I have tried to use;

strInput = Selection.Value 
directory = Split(strInput, "/") 
 
If (StrComp(directory(3), "Lexus", vbTextCompare) <> 0) then
 
            Range(ActiveCell.Offset(0, -3), ActiveCell.Offset(0, 4)).Copy _
            Destination:=Worksheets("Sheet2").Cells(a, 1)
 
            a = a + 1
 
Endif

But this will only work when "directory(x)" is not too large and the string is not too small e.g. selecting directory(5) when the string has only 3 words brings up an error.

Is there any way past this?

Im afraid im new to vba and dont know all the commands.

Any help would be greatly appreciated.

Regards,
Crunchie

 

[machopicho]

Try this:

Sub test()
Dim r, c As Integer
directory = Split(Selection, "/")
r = Selection.Row
c = Selection.Column
a = 1
If (StrComp(directory(3), "Lexus", vbTextCompare) <> 0) Then
 
Range(Cells(r, c + 1), Cells(r, c + 4)).Copy _
            Destination:=Worksheets("Sheet2").Cells(a, 1)
            a = a + 1
 
End If
End Sub

 

[crunchie]

Hi machopicho,

I probably didnt explain myself very well. I have no problem with the code inside the "if" statement it is the conditions of the "if" statement itself that i am having difficulties with.

Since i cant predict the length of the string and position of the keyword in the string itself i am having trouble with the "if" statement.

For example, if the program is pointing at a cell with the following information;

/vehicle/car/Lexus/suspension

and i try to use the following;

strInput = Selection.Value 
directory = Split(strInput, "/") 
 
If (StrComp(directory(5), "Lexus", vbTextCompare) <> 0) then
 
Range(ActiveCell.Offset(0, -3), ActiveCell.Offset(0, 4)).Copy _
Destination:=Worksheets("Sheet2").Cells(a, 1)
 
a = a + 1
 
Endif
 
[CODE]
 
Then i receive an error msg stating "Run Time Error 9: Subscript out of range".
 
I do know from the information that the keyword i am looking for appears in the range of the 3rd to the 6th directory and i tried to use multiple 
 
[CODE]
StrComp(directory(5), "Lexus", vbTextCompare) <> 0

conditions with multiple directory values but i still have the problem of the strings being too short.

If i could find a way to either disable or bypass that error message in my code it would be perfect.

Thank you for the reply though.

Regards,
Crunchie

 

[machopicho]

...i'm not sure to have understood...
Something like this?

strInput = Selection.Value
directory = Split(strInput, "/")
For i = LBound(directory) To UBound(directory)
If directory(i) = "Lexus" Then
n = i
End If
Next
If (StrComp(directory(n - 1), "Lexus", vbTextCompare) <> 0) Then
Range(ActiveCell.Offset(0, -3), ActiveCell.Offset(0, 4)).Copy _
Destination:=Worksheets("Foglio2").Cells(a, 1)
a = a + 1
End If