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

Gnip

New Member
Joined
Jul 23, 2007
Messages
26
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

New Member
Joined
Jul 23, 2007
Messages
26
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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)

Code:
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

New Member
Joined
Jul 23, 2007
Messages
26
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 :oops:
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
ravishankar:
I haven't tried your code, but don't you think there should be an Sub and End Sub line?

Code:
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Code:
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
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top