Find a Item & Copy the Data

Can someone please help me?

I've got a huge SAP generated Parts List from overseas which I need to find 
a unique number in column A. (Sheet1)
There are 10 columns and in excess of 55,000 rows
This SAP Text number is comprised of 18 fixed digits (eg: 
000000065202255411).
However the number itself is variable with leading zero's (eg: 
000000000063060003), and anything in between if you follow my drift.
My problem is I need to verify each customer's price and  purchase cost from 
this data sheet.
I've managed to automate most of the process but I can't return the values I 
need to verify the data using the VBA find function.

I enter the some basic data via a user form which is copied into a 
spreadsheet (Sheet2)
This includes the required item number, minus the leading zero's.
I then want to use this entered number as the trail for finding the number 
in the Sap List and copying the required data back into sheet2 and so 
creating a filtered list for easy validation.
This is all done manually at the moment.
Am I asking too much?

Alec




0
Alec
4/13/2010 9:56:12 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
747 Views

Similar Articles

[PageSpeed] 8

You need to use find with the option lookat:=xlpart.  Since the number
may be in the middle of a number you have to strip off the leading
zeroes and check that the number matches like the code below.  If you
have a number with leading zeroes it may be a string so you need to use
the VAL function to remove the leading zeroes.  Yo also have to use
FindNext becuae the 1st occurance of the number found may not be the one
you are looking for.



FindNum = "0000063060003"
FindNumNoLeading = val(FinNum)

Found = false
set c = columns("A").find(what:=FindNumNoLeading, _
lookin:=xlvalues,lookat:=xlpart)
if not c is nothing then
FirstAddr = c.address
Do 
'remove leading zeroes from found number and compare
if Val(c) = FindNumNoLeading then
Found = True
exit Do
end if
set c = columns("A").findnext(after:=c)
loop while not c is nothing and c.address <> FirstAddr
end if


-- 
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=195154

http://www.thecodecage.com/forumz

0
joel
4/13/2010 10:31:23 AM
Reply:

Similar Artilces:

External Data
Hello All, I built a piviot table pulling external data from DB tables. when I want to edit my query, it will not let me do so in the "Query Wizard" and when I try and make a change in MS Query, I get a dialog box that says "Invalid Cursor State". Are these known issues that have fixes or am I doing something wrong. Thanks ...

Displaying Data
Hi All, I need some urgent help with a database that i'm creating and i'm a bit stuck. I have 2 lists of data they are both linked. I need to create a query where by if a certain criteria matches on the other sheet then all record for that are displaying. For example tasks if 1 assignment has 10 tasks, i need to be able to run a query whereby all tasks are displayed. I'm not sure that makes sense. It does in my head. I've managed it so that it works within my form view. But i need to set up access so that when a person clicks a command button it then open up another form and...

CRM 1.2 Customization Import Cannot Find Webserver
When trying to import customizations into a new blank deployment of MSCRM 1.2, the list where you select the webserver to publish to is blank. I have manually tried to hit the add button, but the system just seems to hang there. I have let it run for over 3 hours without a result. The CRM application runs and works fine, webserver, exchange and sql are all the same Windows 2003 Server via VMWARE. -- Christopher Cognetta Principal Architect / Owner www.cognologysolutions.com the crm web server list in 1.2 was i think populated by a crmsecuritygroup entry under the computer accound in ...

Recommendations on number of items in PST and OST
I need to know whether if there is a recommendation on number of ITEMS in a PST and OST? I am currently running MS Outlook 2003. Thanks I know that there is a limit to the number of items in a single folder in a pst file...it was around 16,000...actually had a user run into it once, and that folder was no longer accessible... -- Susan Conkey [MVP] "Hasher" <Hasher@discussions.microsoft.com> wrote in message news:2864AF80-FD7A-4578-8A80-AB2A32E75993@microsoft.com... > I need to know whether if there is a recommendation on number of ITEMS in a > PST and OST? > &g...

Money 2003 deluxe will not copy to CD
Just as I have said money 2003 will not copy to a CD. It says Microsoft has encountered a problem while copying money any help would be appreciated. There are no passwords installed nor is the program running while I'm trying to copy. If there was a question here, it wins the cryptic question of the day award. The data file won't copy? The application won't copy? You are using Money to try to "copy"? You are using Windows to try to copy? FWIW, Money knows absolutely nothing about burning CDs. It only knows about floppy disks (and a CD is definitely not a floppy d...

storing data from checkboxes
I am creating an access database and am not sure how to handle the following data. I am entering data from an intake form. The Field Name would be ModeOfTherapy the possible "modes of therapy" are: Child Therapy Family Therapy Adult Therapy Couples Therapy The instructions on the form will be to "Check all that apply". I have not yet had to create a check box, but from what I surmise this is something that is created on the Form that will be used for data entry and not in the table formatting the actual field if that makes sense. Are check boxes appropriate in thi...

HELP: Export data to word template and save it as a seperate file
Hi All, I want to export details from excel userform to a word template and save it at a different file. So basically once the user clicks the Export to word button this should happen: -Opens up a dialog window(to select the word template) - User Selects the word template (I have a specific word template designed) - the template is then "SAVED AS" a seperate word file at a specific location(mentioned in code below) But what is happening is, When user clicks the button and selects the template, the data is exported to that template, and its not saved in a differen...

copy multiple lstbx selections to another lstbox etc
(see code below as a reference) I have a list of facilities in a listbox [lstFacAdd] on a form [frm_credreqdetails], pulling from a source table [tbl_cr_facilities]. I currently have it set up so that when I double click on one of those facilities, it adds that facility to another listbox on another form [Forms! frm_CR_ViewFacilities!lstFacilities]. What I want to do is allow multiple selections to be hightlighted from the [lstFacAdd] listbox, and then be simultaneously copied over to the other listbox. maybe a coded button that will take the hightlighted selections and copy them all ov...

Copy Range of Cells from Excel
I have a range of cells on a spreadsheet that I want to copy to Outlook as the body of the message. The range is 9 rows by 9 columns. Three rows have data on them, and each row of data has 2 or 3 sections (2 or 3 columns) of data. These sections are formatted differently - different fonts, different colors, date format, number format, etc. Some sections have borders. If I highlight the range in the spreadsheet, select copy, go to Outlook, and paste, I get an exact replica of what is on the spreadsheet. I want to make this happen using VBA. The code below works partially - ...

Copy an Drag cell Formula Problem
I am suddenly experiencing a problem when I try to copy and drag a formula across a number of cells. For example, I have two different data series in columns A and B and I want the product of the two in column C. Thus, in cell C1 I put “=A1*B1.” I then highlight C1 and drag the formula down the C column. My expected result would be “=A2*B2” in C2, “=A3*B3” in C3, etc. But what is happening is that the results for “A1*B1” is appearing in every cell of column C. However, when I highlight C4, for example, the formula appears as “=A4*B4” but the product is the same as that found in C1 ...

copying word tables that have carriage returns in a cell
Is there a way to properly copy table cell data containing 'internal' carriage returns into an equivalent cell in Excel? "Pete Webb", In the (Edit >>) Replace dialog in Word 6, there's a More button. Click it & there's a Special button. One option is manual line break, selecting this puts the relevant character into the Find What box. Replace with something unique, eg: $$$. Copy the table. In Excel, (Edit >>) Paste Special as Text. Then Replace again ; Find What = $$$. Click in the Replace With box, type 0013 on the number keypad while holding dow...

Print three copies and send end of document for crystal reports
I am looking for a tool that will allow me to send a code from crystal that says print the page 3 times and then send an end of document so the printer will print three copies and then staple. The report will generate multiple work orders but before the next work order number I want it to print 3 copies and send an end of document to the printer so it will print the three pages and then staple. Crystal wants to send an end of document either per page or per report. Is this something anyone has come across or can do? Well, one way to accomplish this would be to send one document with t...

Excel file goes from 12 to 38 MB after "Get External Data"
Hi In an excel spreadsheet, there are three worksheets that are populated with data in perhaps 20 columns and up to 3000 rows, (along with several other worksheets in the same spreadsheet) The spreadsheet is 12 MB with the hard-coded (hand keyed) data sitting in each of these three worksheets. We decided that we wanted to import the data to these pages from SQL Server rather than maintain it manually, so we wrote a macro that clears the three worksheets and then executes the "Get External Data" function for each of the three worksheets. However, when we run the macro and it rep...

Find cause of error when calling CFrameWnd::Create()
Hello, in my application I had a call to CFrameWnd::Create() return 0 (which indicates an error), so my question is: Is there a function I can call to get a more detailed description of the error, maybe something mfc specific? I tried a simple ::GetLastError(), but it returned 0.... Eric Lilja wrote: > Hello, in my application I had a call to CFrameWnd::Create() return 0 (which > indicates an error), so my question is: Is there a function I can call to get > a more detailed description of the error, maybe something mfc specific? I > tried a simple ::GetLastError(), but it re...

How do I import fractions without data being converted to dates?
I am importing data that was created in France and they used fractions for the inch data rather than decimals. Now, when I try to import that data, even though the preview shows it importing correctly, it converts the fractions to dates because I suppose it sees the forward slash. How do I make it recognize them as fractions and not dates? Thanks, Ray Proeber rproeber@execpc.com Ray, Format the column for text in the import wizard. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "rproeber" <rproeber@discussions.mi...

I want to copy only the layout and formulas and not the exisiting
I want to copy an exisiting worksheet with all the formulas and layout but don't want to take all the data contained within the sheet so that I can resuse the existing layout? Any ideas? Depending on your actual sheet layout, this might be workable ... After you copy over the entire sheet, you can press F5 > Special > Constants > OK to select the data*. Then clear with the Delete key *includes col/row labels etc, if any, unfortunately And then re-copy > paste over the col labels as required (< this is the part which might prove a bit tedious to do) -- Rgds Max xl 97 --...

Copying functions from one cell to other cells
I have a question I was hoping someone could help me answer. I have the function =IF(I22>M20-1,-M20,IF(J22>M21-1,M21,N22)) in cell M22. Now I would like to find a quick way to copy this to cells M23,M24,M25.....and beyond. The problem is I want the some of the function to change when I copy it and some of it to stay the same. I would like the parts "M20-1,-M20" and "M21-1,M21" to stay the same while the other move down with each cell. For example in cell M23 (the next cell down in my range) I would like the function to read =IF(I23>M20-1,-M20,IF(J23>...

Waiting for data to be returned from Microsoft Query
I'm having a problem with Microsoft Excel/Query. From Excel when I go into Data/Get External Data/New Database Query I get this "Waiting for data to be returned from Microsoft Query". I have completely removed MS Office and reinstalled. Occasionally (1 out of 100) the ODBC Data Sources will appear, but very seldom. I also tried to open Microsoft Query and it won't even open. I've tried uninstalling and reinstalling multiple times. I've searched online and couldn't find any answers. I found other people with the same problem, but nobody with an answer. Do...

Data Base Size
I have a question on database size. What constitutes the database size in exchange 2003. Is it the priv1.edb + pub1.edb or is it all of the edb files and all of the stm files? On Mon, 10 Oct 2005 05:08:03 -0700, Sam <Sam@discussions.microsoft.com> wrote: >I have a question on database size. What constitutes the database size in >exchange 2003. Is it the priv1.edb + pub1.edb or is it all of the edb files >and all of the stm files? priv1.edb + priv1.stm= one mailbox store pub1.edb + pub1.stm = one public store. How can I reduce the size of my priv1.stm database I am c...

find problems
hello, I want to let excel find in a range of cels a certain tekst and get the number in the cel right from that tekst. The tekst is in the range sometimes more than once. If that is it has to calculate the sum of the numbers in the cells right from the tekst. Greets Ivo Hi not really sure what you want to achieve then more trhan one occurence exist. Could you post some examples -- Regards Frank Kabel Frankfurt, Germany "ivo bodewes" <ivo.bodewes@wanadoo.nl> schrieb im Newsbeitrag news:417a6c61$0$76540$b83b6cc0@news.wanadoo.nl... > hello, > > I want to let e...

Charts generated by Data Analysis/Regression Add-in
I've tried this on three different Excel 2007 installations in both 2003 and 2007 file formats: When I run a regression model on a set of data, the charts generated by the Add-in cannot be moved, resized, etc. without the chart literally "falling apart". Specifically, the Residual Plots, Line Fit Plots, and Normal Probability Plots. When I click on a chart to resize or drag it, parts of the chart move properly while other parts are left behind... Are they real charts, or collections of shapes that look like charts, and what you're doing is dragging around a subset of thes...

User defined Function to use data direct from table???
I am trying to create a Function that will perform a calculation that will be referenced by other Subs and or Functions. I would like to access data in a table directly from within the Function. Is this possible? I can find nothing that says yea or nay. Thanks. Depending on what you're trying to do, you can either use the DLookup aggregate function, or you can open a recordset and loop through it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Weeble" <Weeble@discussions.microsoft.com> wrote in message ne...

combining items in secondary table
I "inherited"a database for an organization that came from excel. It now has a primary table of people names and a secondary table with names of all children for some of the people in primary table. I need to do a report that will allow me to list ALL of the children's names on the same line. I can't figure out how to do this. The originial database has a field for each child in the primary table (6 fields) and I converted it to a secondary table. Actually it would be easier to combine the data from the original design but I don't want to have all of ...

updating or changing data
Background: For a construction business, I use a Workbook Template fo each job. This template contains a separate worksheet for each Labor Equipment, Material, Other, and Subcontract Prices. Each of thes sheets has a different amount of "fields" to suit each individually. then have a sheet that I use for the estimate, and up to 60 worksheet for individual days to track or bill the job out. I have created ID' for each item in my pricing databases and use the VLOOKUP function t enter the appropriate items on the estimate or daily record sheets. The daily record sheets are set ...

Protecting excel sheet for data input
Dear All: I am using Excel for inputting data as part of a larger application. Each worksheet roughly corresponds to a table for user input. Each table has a predefined number of columns, the rest of the columns in the worksheet are not being used and can be hidden from the user. My concern is about the data area of the worksheet, which I am not protecting in any way because I want the user to be able to modify the data at will. During this process, the user may, for example, accidentally delete a useful table column. So I am looking for a way to at least prevent column deletion while allowin...