Determining how many numbers are in a string

[Martanna]

I have a homework assignment requiring me to write a macro to have the user change his/her password. The requirements are; it must be 8 characters, the first character must be a letter, all letters must be uppercase, no blank spaces, and there must be only 2 numbers. Everything worked fine until I got to the 2 numbers bit, I am drawing a complete blank.
Even a nudge in the right direction would be greatly appreciated!

 

[hiker95]

Martanna,

Welcome to the MrExcel forum.


Tips for posting a question on the board
http://www.mrexcel.com/forum/faq.php?faq=mrexcel_faq#faq_no_soliciting
We are not here to do your work (or your homework) for you. Before posting a question or request for help, please put in time and effort on the problem yourself.


Can we see what you have so far?


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[Martanna]

I am using Excel 2007.
I am using an outer and inner loop. The inner loop tests the password, and the outer loop is to verify it. The following is what I have to test everything but the 'must contain 2 numbers' condition.

'test length
intLength = Len(strPassword)
If intLength <> 8 Then
MsgBox ("The password you entered does not have 8 characters, please try again.")
strPassword = InputBox("Enter a new password. The password must be 6 uppercase letters 2 numbers, start with a letter, and be 8 characters long with no spaces.")

'test if uppercase
Else
If strPassword <> UCase(strPassword) Then
MsgBox ("The password you entered is not uppercase, please try again.")
strPassword = InputBox(".........")

'test if first character is a letter
Else
If (strPassword Like "[0-9]*******") Then
MsgBox ("The password you entered does not start with a letter, please try again.")
strPassword = InputBox("........")

'test for spaces
Else
If InStr(strPassword, " ") <> 0 Then
MsgBox ("The password you entered has spaces, please try again.")
strPassword = InputBox(".......")

'password is valid
Else
blnValid = True

This all work great, I am able to verify password. It's just the testing for 2 numbers that I am stumped on. The only thing I have been able to find any information on is testing if the whole string is numeric or letters.

 

[hiker95]

Martanna,


In the future please post all your macro code, and wrap the code in code tags.


At the beginning of your posted code, enter the following without the * character:
[*code]


'Your code goes here.


At the end of your posted code, enter the following without the * character:
[*/code]





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

'Two more variables to loop and count how many numbers are in strPassword
Dim a As Long, acnt As Long
acnt = 0
For a = 1 To 8 Step 1
  If IsNumeric(Mid(strPassword, a, 1)) Then acnt = acnt + 1
Next a
If acnt = 2 Then
  'We have 2 numbers
Else
  MsgBox ("The password you entered does not have 2 numbers, please try again.")
  strPassword = InputBox("Enter a new password. The password must be 6 uppercase letters 2 numbers, start with a letter, and be 8 characters long with no spaces.")
End If

 

[Martanna]

Thank you very much for the help! I had tried something similar to this, but when it didn't work, I thought my logic was wrong. What I discovered was that I had to use it at the beginning of the loop as the first condition.

Martanna