VLOOKUP only works on data manually entered.

[Guest]

I have a workbook with several worksheets. I have tried to limit user
interaction and have some security processes built in. So what I have done
is made a lookup range called “Passwordâ€. This range is made up of three
columns: Column E “Passwordâ€, Column F “Name†Column G is “Initialsâ€. It
covers rows 17 thru 50. The way it works is a command button brings a
userform to add a name. When the name is added to Column F the formula
=IF(ISERROR(UPPER(LEFT(F17,1)&MID(F17,FIND(" ",F17)+1,1)&MID(F17,FIND("
",F17,1)+3,1))),"",UPPER(LEFT(F17,1)&MID(F17,FIND("
",F17)+1,1)&MID(F17,FIND(" ",F17,1)+3,1))) in column G reads column F and
places the persons initials in column G. At the same time Column E with the
formula: =IF(ISTEXT(F17),M17,"") reads column F and if there is text it
places a 4 digit password number from column M. These numbers are generated
by a macro that places them in M17:M50 and can be changed or updated by
re-running the macro. Column “M†is now hidden. Now I have the platform to
use for the security issues. In other worksheets it is necessary for the
users to place their initials in the forms that I have built. Using the
formula: IF(ISERROR(VLOOKUP(D38,Password,3,0)),"",VLOOKUP(D38,Password,3,0))
I should be able to enter the four number password associated with the
persons name and it should place their initials in the appropriate cell. The
problem is, it does not work! If I manually enter a number in the password
column it works, but with the random numbers generated by the macro it does
not work. I thought it might be the formula in column E that was giving me
grief, so I changed the range in the macro to place the number directly into
column E, then use conditional formatting to hide the ones not in use. That
did not work either. When I mean it does not work, when the password in
entered into the userform, which places it in the appropriate cell, nothing
happens. Like it is a wrong password for the name. Does this have something
to do with the range of random numbers generated by the macro? I would like
the functionality to update the passwords from time to time for security
reasons; otherwise I would just manually enter some in and be done. Any
ideas would be appreciated!!

Mike Rogers
PS: EVERYTHING I have put into the project I have learned from this forum,
THANKS everyone!!!!

 

[Biff]

Hi!

First thing that comes to my mind is that the macro generated "numbers" are
really TEXT strings?

The "tip-off" is that as you say, if you then manually enter a number , then
it works.

Biff

 

[Guest]

Biff,

Thanks for the response... You were right on!!! I looked in the macro and
here is the formula that gives me the grief:
myCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")" Now for the
next question. Can I just remove the word "TEXT" from the formula. Or what
do I need to change.

Thanks again
Mike Rogers

 

[Guest]

Removing the word "TEXT" does not work, no suprise to you though. I did get
it working, but I think there is a better way. What I did was multiply the
range by one and it works, but I would think there was a "cleaner" way of
addressing the problem.

Mike Rogers

 

[Biff]

I would think there was a "cleaner" way of

addressing the problem

Actually, that's a pretty good way to do it.!

Or:

=--TEXT(ROUND(10000*RAND(),0),"0000")

Biff

 

[Guest]

Biff

Thanks for the help and thaks for being on this forum helping all of us
that are learning.

Mike Rogers

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff

Thanks for the help and thaks for being on this forum helping all of us
that are learning.

Mike Rogers