Pulling data from cells

[Gdel]

HI all,

This is my first post here, infact i registered to make it, ive lurked on the boards for a while now but i cant find a answer to this question anywhere.

Let me explain what im trying to achive first. I work for a fault management centre for a large ISP, ive been given a spreadsheet with many rows of info to analyse and theme like faults our customers are having. I would normally use SQL for this sort of thing however for reasons beyond my understanding i have to do this manually everyday and it is taking far too much time, so i would like to automate my sheet, great idea but as a Excell novice (basic VLOOKUP is my limit) i need help to do this.

The first step to this is for me to take the fault description column and identify the fault, ill give you a example cell entry.

2009/03/07 ERROR 721/IPSTREAM/

What I need help with is a command that will identify a given text entry and copy the row into a themed tab, in this case i would like the command to scan the description column and copy (so it leaves the original data in the master tab) the row that contains the text 'IPSTREAM' into the IPSTREAM tab, if thats even possible?

Hope my disjointed ramble is able to be understood and that some kind soul can help.

Cheers,

 

[stanleydgromjr]

Gdel,

Welcome to the MrExcel board.

See:
DistributeRows, by Norie:
http://www.mrexcel.com/forum/showthread.php?t=315083


Try (very user friendly):
Excel Explosion 3.0 (free addin) at:
http://www.datapigtechnologies.com/freeware.htm
View the demo.


Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[Smitty]

Welcome to the Board!

See if this gets you started:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Sheets("Data").Range([A1], Cells(Rows.Count, "A").End(xlUp))<br>        <SPAN style="color:#00007F">If</SPAN> InStr(c, "IPSTREAM") <SPAN style="color:#00007F">Then</SPAN> c.EntireRow.Copy Sheets("IPSTREAM").Cells(Rows.Count, "A").End(xlUp).Offset(1)<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I only wrote the IPSTREAM condition, but you can just copy that If InStr row and adjust it for as many conditions as you need.

Hope that helps,

 

[Gdel]

Welcome to the Board!

See if this gets you started:

Sub foo()
Dim c As Range

For Each c In Sheets("Data").Range([A1], Cells(Rows.Count, "A").End(xlUp))
If InStr(c, "IPSTREAM") Then c.EntireRow.Copy Sheets("IPSTREAM").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Next c

End Sub

I only wrote the IPSTREAM condition, but you can just copy that If InStr row and adjust it for as many conditions as you need.

Hope that helps,

Smitty, Cheers for your help!

Now here are the 'noob' questions so i can modify this script as required.

I have created a VB script and linked it too a macro button.

However im a bit confused as to the variables.

("Data") = The tag for the active tag, so this can be changed to the name of the tab containing all the raw data?
(c, "IPSTREAM") = The operative field, so i could change this to any text i need to filter?
Sheets("IPSTREAM").Cells(Rows.Count, "A").End(xlUp).Offset(1) = This controlls where the text is deposited too?

thanks for your help.

 

[Gdel]

The above script also gives a 'subscript is out of range error' how would i modify this?

 

[Smitty]

However im a bit confused as to the variables.

Sorry for not adding some comments to the code for you, but I'll clarify using your question:

("Data") = corresponds to the sheet name where your primary data is stored, i.e. the data you're trying to analyze and copy to the other sheets. I usually choose that name for any sheet that houses primary data. Just swap it with your actualy sheet name.

(c, "IPSTREAM") = The operative field, so i could change this to any text i need to filter? Yup.

Sheets("IPSTREAM").Cells(Rows.Count, "A").End(xlUp).Offset(1) = This controlls where the text is deposited too? You got it.

The code works like this:

1) Look in each row of data in the source sheet
2) If the specified string is found within the row (that's what InStr does), then copy the entire row
3) the part after the "Then" is the Destination sheet and range, so in this case Sheets("IPSTREAM") is the IPSTREAM sheet. You can change both the InStr search term and sheet name in additional tests.
4) The End(xlUp) part tells the code to start from the bottom of the column (A in this case), go up to the first row with data, and paste the copied row to the row beneath it, which is empty.

HTH,

 

[Smitty]

The above script also gives a 'subscript is out of range error' how would i modify this?

I'll bet that happened for one of two reasons:

1) you don't have a sheet named Data or 2) you weren't on it when you tried to run the code. The Data sheet (whatever you name it) needs to be the active sheet when you run the code.

The specific error means that VBA can't find the specified reference when the code compiles. You can see what it's looking for in the VBE; it'll be highlighted in yellow. If you're not sure what it is, just post back the offending line of code.

 

[Gdel]

Smitty again, thanks very much for your help.

Sub foo()
Dim c As Range

For Each c In Sheets("All Queues").Range([A1], Cells(Rows.Count, "A").End(xlUp))
If InStr(c, "IPSTREAM") Then c.EntireRow.Copy Sheets("IPSTREAM").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Next c

End Sub

Ive modified the code as required, but still 'subscript out of range' error, from my understanding the only part of the code that could give this error is ("All Queues") range and the destination ranges ("IPSTREAM").

which both appear to be correct (went so far as to copy the text from the tabs themselves).

 

[Smitty]

Are you on the All Queues sheet when you run the code?

Check your PM.