VBA- Data Mgt

[quej2003]

Hi All,

Got a couple of questions wouldn't mind being answered:

1- I have some data that is import along with a load of crap surrounding it kinda of like below:

crap
crap
crap

identifier
Data
Data
Data

More crap

Was wondering how in macro you would write it such that can look through the column A and find the identifier (just a text string) then delete all the rows above it including the identified, then all the rows below the data.

2- Have another data set, from column A to AZ + variable number of rows,looking to search for the first intsance of multiple criteria in the top row from right to left (starting from right I guess I mean) then when one of the criteria is found copy the column it is in and the 3 columns to the left of it into a different sheet that has a similiar name to one this data is in: so data is in "jan 2011" to be copied into the existing sheet of "jan 2011 data"

Cheers

 

[quej2003]

small bump (am I allowed to bump if any does read this?)

Also wondering is a good book to start with regards to writing of VBA/macros/etc. ? I am happy reading through someone else's macro and sometimes takes me 10 mins to get my head around it. But when I try to write anything myself, I get unstuck on what seem like simple things (as above), any tips?

 

[VoG]

See if this helps http://www.mrexcel.com/forum/showthread.php?t=536240

 

[quej2003]

See if this helps http://www.mrexcel.com/forum/showthread.php?t=536240

Hmm tried to fiddle around with it, still no joy, i guess a small rephrase

I have something like

123
1234
12345

12

Ident
Data1
Data2
Data3

456
4567
45678
456

The Ident is what am looking for, and its always in column A, I just want to get at the Data1,2 and 3 really by deleting everything above <s> and it, as well as everything below the Data3!

 

[VoG]

Try

Sub atest()
Dim Found As Range
Set Found = Columns("A").Find(what:="Ident", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Not found", vbInformation
Else
    Range("A1:A" & Found.Row).EntireRow.Delete
End If
End Sub

 

[quej2003]

Try

Sub atest()
Dim Found As Range
Set Found = Columns("A").Find(what:="Ident", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Not found", vbInformation
Else
    Range("A1:A" & Found.Row).EntireRow.Delete
End If
End Sub

I try and I get the following error- compile error:Invalid or unqualified Reference - highlighting the .Range

changing to just range since i added that . myself i think, i find nothing, most odd I think

 

[VoG]

Is it exactly "Ident", not "Ident." or "Ident " for example?

 

[quej2003]

Is it exactly "Ident", not "Ident." or "Ident " for example?

the exact is in fact: < s >

without the spaces between the <>

 

[VoG]

Try replacing Ident with < s >

 

[quej2003]

Try replacing Ident with < s >

Ya, had changed that before I changed the search to look just for the part instead of whole and seems to have worked

lookat:=xlWhole
to
lookat:=xlPart

Thanks for the patience by the way, would you suggest with regards to learn more to get some books or just keep going to forums like here and asking questions when stuck?