Find a Item & Copy the Data

[Alec Coliver]

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

 

[joel]

You need to use find with the option lookat:=xlpart. Since the numbe
may be in the middle of a number you have to strip off the leadin
zeroes and check that the number matches like the code below. If yo
have a number with leading zeroes it may be a string so you need to us
the VAL function to remove the leading zeroes. Yo also have to us
FindNext becuae the 1st occurance of the number found may not be the on
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