Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Item & Copy the Data
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/sh...d.php?t=195154 http://www.thecodecage.com/forumz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To find rate of each item from item.xls and to copy price.xls | Excel Discussion (Misc queries) | |||
Find and copy data | Excel Worksheet Functions | |||
Find rows with a common item and find or highlight difference | Excel Programming | |||
how do i find multiple matches of one data item in an excel range | Excel Discussion (Misc queries) | |||
Find All and then Copy the Data... | Excel Programming |