Probably simple but..... Pulling out info based on text

[Gnip]

Hello gang,

Brand new to this forum, very impressed!!!

I have what is probably a simple one to solve but my tiny mind cannot cope......

Within Excel I have a regular table of data and dependent on the info within a column of cells I would like the info to appear within one of 3 new sheets. BUT.... I can't figure it out because the info forms an irregular part of text within the cells in a specific column - Obviously if it was dependent on the whole of the text I could use Vlookup but it's not.

If it needs further clarification, a column of cells may have, say, "blahblah234/PLblah" -> If this cell contains within the text "PL" then it should have the whole of the row in the new sheet, if not then blank.

Help!!!!
Thanks
Gnip

 

[ravishankar]

Hi
I assume the search is made on col A and I can find rows with partial matches. How do I know which sheet of the three it should go to?
Ravi

 

[Gnip]

Hi,

I can always re-jig so the index column is A.

The pointing to one of 3 sheets can be downgraded if necessary, thus on 1 blank sheet if the "pl" appears in a cell, that enitre row is displayed, if not it doesn't - I could always edit to say "Blah" of "PL" instead and re-do on a fresh s/sheet.

If that makes sense.
Thanks

 

[ravishankar]

Hi
No need to compromise on your requirements. We are trying to understand what you need so that it can be coded accordingly.
Paste the following codes in the macro window ( Alt F8)

x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
c = InputBox("enter search string")
d = 1
For a = 1 To x
b = Cells(a, 1)
If InStr(b, c) > 0 Then
Rows(a).Copy
Worksheets("sheet2").Rows(d).PasteSpecial
d = d + 1
End If
Next a

Run the macro. It will list all PL cotaining rows to sheet 2
Ravi

 

[Gnip]

Hi Ravi,

Thanks for your help.

A slight issue, it falters at top line x1Up with message Invalid outside procedure - Bound to be something I'm doing wrong....

Thanks

 

[ravishankar]

HI
Do you have any other procedure or macro installed in your workbook? if yes, disable them or remove them and try this macro.
Ravi

 

[Scott Huish]

ravishankar:
I haven't tried your code, but don't you think there should be an Sub and End Sub line?

Sub test()
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
c = InputBox("enter search string")
d = 1
For a = 1 To x
b = Cells(a, 1)
If InStr(b, c) > 0 Then
Rows(a).Copy
Worksheets("sheet2").Rows(d).PasteSpecial
d = d + 1
End If
Next a
End Sub

 

[ravishankar]

Hi
When alt F8 is pressed and a macro name is given, sub and end sub automatically appears. My macro codes are inserted in between. (may be if you choose Alt F11 and insert a new module, you need to add sub / end sub)
Ravi

 

[Norie]

Ravi

That will only happen if you hit the Create button.

Wouldn't it be easier to actually include Sub and End Sub when posting code.

 

[Scott Huish]

My macro codes are inserted in between.

Yes, but you didn't say that, which is why the op is getting an error. I would tend to agree with Norie, that it would be easier to post with it.

Gnip:
Another possibility:

Sub test()
Dim x As String, d As Long
d = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
x = InputBox("String to search for")
With Columns("A:A")
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="=*" & x & "*"
    Range("A2:A" & Rows.Count).EntireRow.Copy Worksheets("Sheet2").Range("A" & d)
    .AutoFilter
End With
End Sub