Prev: Locale issue with union operator string (,)
Next: Copy sheets to new workbook - subscript error
From: Alec Coliver on 13 Apr 2010 05:56
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




From: joel on 13 Apr 2010 06:31

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

 | 
Pages: 1
Prev: Locale issue with union operator string (,)
Next: Copy sheets to new workbook - subscript error