Hi...

I am directing this question to the MVP'ers because it is very difficult (in
my opinion).

Here is the situation...

I am writing a Macro to automate as much of an importing process that I can.
I am happy to say that I am on the last steps. Here is what I am trying to
do now -

I currently have to manually verify that all accounts in an imported
document are correct, and what they should be. I have a printed list that I
check the imported file with. I would like for excel to look at that list
and somehow tell me if the accts are correct....I only have to verify that
the first 5 numbers of an 11 digit account are correct. If they are not
correct, I would like a box to pop up saying "Accts Incorrect" and maybe
reference the cell. In the file itself, there can be any number of accounts
to check (from 1 to 100000)...(very tedious).

Any direction at all??????

It is very easy.

Assuming that the data is in A2:An, and the list is in Sheet2!A:A

Dim i As Long
Dim iPos As Long

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
iPos = 0
On Error Resume Next
iPos = Application.Match(Val(Left(Cells(i, "A").Value, 5)), _
Worksheets("Sheet2").Range("$A:$A"), 0)
On Error GoTo 0
If iPos = 0 Then
Cells(i, "A").Interior.ColorIndex = 3
Else
Cells(i, "A").Interior.ColorIndex = xlColorIndexNone
End If
Next i

I have coloured the offending items rather than a msgbox, very intrusive.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Frantic Excel-er" wrote in
message ...
Hi...

I am directing this question to the MVP'ers because it is very difficult
(in
my opinion).

Here is the situation...

I am writing a Macro to automate as much of an importing process that I
can.
I am happy to say that I am on the last steps. Here is what I am trying
to
do now -

I currently have to manually verify that all accounts in an imported
document are correct, and what they should be. I have a printed list that
I
check the imported file with. I would like for excel to look at that list
and somehow tell me if the accts are correct....I only have to verify that
the first 5 numbers of an 11 digit account are correct. If they are not
correct, I would like a box to pop up saying "Accts Incorrect" and maybe
reference the cell. In the file itself, there can be any number of
accounts
to check (from 1 to 100000)...(very tedious).

Any direction at all??????

Hi Frantic,

You don't really need a macro (or an MVP).

Let's I assume that the data is imported in an Excel worksheet using the
Data - Import External Data menu command. Then you have a list with Account
No, and a few other columns - like this:

Account No Stuff1 Stuff2 Stuff3
12345678 xxxxx yyyy zzzz
45612349 xxxx1 yyy1 zzz1

In another worksheet, build yourself a list of all the valid accounts. Just
the first five digits if you like:

Valid Ac
12345
12222

Name this range 'ValidAccount' use a dynamic range if you are likely to add
to the list.

Now, go back to the imported data and add a column at the right of the data.
Give it the heading 'Valid'. Then copy the following formula in the new
colum
=NOT(ISERROR(VLOOKUP(LEFT(A2,5),ValidAccount,1,FAL SE)))
The formula uses a VLOOKUP function to check the first 5 digits in Account
No against the ValidAccount range. If it cannot find a match it wil return
an error. Use NOT because you want to find the entries that do match.

Alternatively, you can use the following formula, if it makes more sense
(results are the same)
=ISNUMBER(MATCH(LEFT(A2,3),ValidAccount,0))

The imported data now looks like this:

Account No Stuff1 Stuff2 Stuff3 Valid
12345678 xxxxx yyyy zzzz TRUE
45612349 xxxx1 yyy1 zzz1 FALSE

Change the imported data range properties - make sure that the 'Fill down
formulas in columns adjacient to data' check box is ticked.
Create a dynamic range called 'ImportData' that encompasses the imported
data range and the 'Valid' column.

Now build a Pivot Table with 'ImportData' as its data range, 'Valid' as a
page field, 'Account No' as a row field, and count of Account No as the data
field.

Select FALSE from the page field drop-down.

Done

Ed Ferrero
http://edferrero.m6.net



"Frantic Excel-er" wrote in
message ...
Hi...

I am directing this question to the MVP'ers because it is very difficult
(in
my opinion).

Here is the situation...

I am writing a Macro to automate as much of an importing process that I
can.
I am happy to say that I am on the last steps. Here is what I am trying to
do now -

I currently have to manually verify that all accounts in an imported
document are correct, and what they should be. I have a printed list that
I
check the imported file with. I would like for excel to look at that list
and somehow tell me if the accts are correct....I only have to verify that
the first 5 numbers of an 11 digit account are correct. If they are not
correct, I would like a box to pop up saying "Accts Incorrect" and maybe
reference the cell. In the file itself, there can be any number of
accounts
to check (from 1 to 100000)...(very tedious).

Any direction at all??????