Looking for help with a macro/vba for find/copy/paste

[Closed]
Report
Posts
6
Registration date
Sunday February 1, 2015
Status
Member
Last seen
April 29, 2015
-
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
I have a lot of data in column A of a spread sheet. I would like to "search" for part of a string, and then copy the row of the cell with this data into another sheet or column. I have about 28,000 part numbers in column A and some of them end in .00a, or .00b, and so on. The ones that have this suffix need to be isolated from the rest of the data. Does anyone have some code or suggestions on how to do this? I am not an expert in the area, so looking for the VB code that is close so I can tweak.

Also, after these are isolated, I need to identify the highest letter, as below the 01370579 would be letter l.

Thanks a million.

Example below of data in Column A.


01370579.00a
01370579.00f
01370579.00g
01370579.00h
01370579.00j
01370579.00k
01370579.00l
1370579
01370580.00a
01370580.00f
01370580.00g
01370580.00h
01370580.00j
01370580.00k
01370580.00l
01370580
01370685.00b
01370685
01370686.00a
01370686
02010584.00A

3 replies

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
This is something that you don't see much of on here but it is very worthwhile:


Sub SQL()
' Requires "Microsoft ActiveX Data Objects 6.1 Library"
' Add using Tools, Reference

'Workbook MUST be saved at least once for this to work

Dim sqlstr As String
Dim rs As New ADODB.Recordset
Dim conn As ADODB.Connection

'######################################################################
'## Turn off all Application updating stuff
'######################################################################
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Set conn = New ADODB.Connection

MyName = ThisWorkbook.Name

myPath = ThisWorkbook.Path
MyFullname = ThisWorkbook.FullName

strConn = "DSN=Excel Files;DBQ=" & MyFullname & ";DefaultDir=" & myPath & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

conn.Open strConn
conn.CommandTimeout = 0

' Find a unique list of non blank parts WITHOUT the suffix
' and the highest value of the last character for that part.
' where the last character is between A and Z

sqlstr = ""
sqlstr = sqlstr + "select distinct left(part,len(part)-4) as part2, "
sqlstr = sqlstr + " MAX(right(part,1)) as maxright"
sqlstr = sqlstr + " FROM [" + ActiveSheet.Name + "$]"
sqlstr = sqlstr + " WHERE part IS not NULL "
sqlstr = sqlstr + " and ucase(part) like '%[A-Z]'"
sqlstr = sqlstr + " group by left(part,len(part)-4)"

'Run It!
rs.Open sqlstr, conn

' format range as Text
ActiveSheet.Range("D2:E" & Rows.Count).NumberFormat = "@"
' Clear the area
ActiveSheet.Range("D2:E" & Rows.Count).ClearContents
' put the dataset retrieved into the range
ActiveSheet.Range("D2").CopyFromRecordset rs

'close the open connection and recordset
rs.Close
conn.Close
Set conn = Nothing

'######################################################################
'## Turn on all Application updating stuff
'######################################################################
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
Results look like this:


Hopefully it is what you wanted.
If not, please supply an example of the output you want.
Posts
6
Registration date
Sunday February 1, 2015
Status
Member
Last seen
April 29, 2015



Thank you very much for your work on this. I have been trying to get your code to run on my workbook. I did add the Active X library as stated. However when I try to run the Macro, I get the following error.


System Error &H80040E10 (-2147217904)

I'm sure I have something not quite right.

I really appreaciate your efforts, and I have learned a great deal by studding this example.
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
Try downloading this and running it and see if you get the same error.

http://speedy.sh/cP2UT/sqltest2.xlsm

Which part is causing the error? Can you step through it using F8 to see.

Can you upload your sheet? Send link to me via Private message.

Do you have Access installed? It might be Jet related.
Posts
6
Registration date
Sunday February 1, 2015
Status
Member
Last seen
April 29, 2015

Your sheet seems to work great! Thank you again for the help. I'll let you know if I need any other tweaks.
Posts
6
Registration date
Sunday February 1, 2015
Status
Member
Last seen
April 29, 2015

Hi Ray H.

I could use your help on one additional detail. Some of the part numbers have values greater that Z. As you know I told you we wanted to identify the "largest" letter from A-Z, however in some cases we go to AA-AZ after Z has been used. Can you please help me with the code to where it will determine the "largest" between A and AZ? I know the area of the code that requires modification, but I'm afraid I will mess it up if I try to change it.

Also, I could also use a macro that would delete a row if a string of text in the cell of that row contains a certain string of text. It would be good if I could also input a wild card. Example would be that cell A2 contains 03E58302 and I said any cell in column A that contains 03E***** then delete row 2....and so on. If this is hard to do nevermind as my first request is more important. Thanks and I hope to more skilled at this soon.
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24 >
Posts
6
Registration date
Sunday February 1, 2015
Status
Member
Last seen
April 29, 2015

This code should do what you need:

sqlstr = ""
sqlstr = sqlstr + "select distinct left(part,instr(part,'.')-1) as part2, "
sqlstr = sqlstr + " MAX(right(part,len(part)-instr(part,'.')-2)) as maxright"
sqlstr = sqlstr + " FROM [" + ActiveSheet.Name + "$]"
sqlstr = sqlstr + " WHERE part IS not NULL "
sqlstr = sqlstr + " and (ucase(part) like '%[A-Z]'"
sqlstr = sqlstr + " or ucase(part) like '%[A-Z][A-Z]')"
sqlstr = sqlstr + " group by left(part,instr(part,'.')-1)"

I would like to see the full list if that's possible so that all bases and combinations can be allowed for.
You can use www.speedyshare.com to upload the list. If you feel that the data is confidential to be spread around for all to see then please send the link to me as a private message.

How certain are you that there is always '00' following the period?

Are there instances where you could have '01370579.00a' and '01370579.00A' while being very similar are entirely different parts? As right now it assumes a lowercase 'a' is the same as an uppercase 'A'

Regarding your 2nd request, please post it as new question as it is quite different from this one.