Rand to return Alphas (2003 SP3)

[steveh]

Good afternoon

I currently generate unique numeric passwords by using this formula

=ROUND(RAND()*(A1-A2)+A1,0)

Up until now it has been sufficient for my needs to return an 8 numeric number by putting 10000000 into A1 an 99999999 into A2 and when I press F9 my target cell recalculates the number.

I have however been asked for it to be alpah numeric so I thought I could change my numeric parameters to report a rand from 1000 - 9999 and then adjust the code and have 4 alphas returned in another cell and then combine both results. I have tried using CHAR() within the code but I just can't seem to grasp it.

Any pointers please?

[HansV]

To generate a random upper case letter, use CHAR(65+26*RAND()). So to generate a string of 4 random upper case letters, use

=CHAR(65+26*RAND())&CHAR(65+26*RAND())&CHAR(65+26*RAND())&CHAR(65+26*RAND())

[sdckapr]

If you have 1000 - 9999 (BAAA-JJJJ with 0=A, 9 = J) you can transform this 4-digit to Apha with (presuming A3 has the 4-digit number):
=CHAR(MID(A3,1,1)+65)&CHAR(MID(A3,2,1)+65)&CHAR(MID(A3,3,1)+65)&CHAR(MID(A3,4,1)+65)

But do you only want alpha chars from A - J (0-9), or do you want all 26 possibilities?

A 4 digit alpha sequence with A-Z can be obtained from:
=CHAR(INT(RAND()*26+65))&CHAR(INT(RAND()*26+65))&CHAR(INT(RAND()*26+65))&CHAR(INT(RAND()*26+65))

You can change the "26" to the number of chars you need. The "65" represents the char of the "A".

Steve

[steveh]

To generate a random upper case letter, use CHAR(65+26*RAND()). So to generate a string of 4 random upper case letters, use

=CHAR(65+26*RAND())&CHAR(65+26*RAND())&CHAR(65+26*RAND())&CHAR(65+26*RAND())

Thanks Hans

For once I was barking up the right tree (a bit) and had got as far as =CHAR(65+INT(RAND()*26)) which return a random letter and I was then trying to combine it with the 2 cells I was using *A1-A2)+A1,0) but not quite getting there.

[steveh]

If you have 1000 - 9999 (BAAA-JJJJ with 0=A, 9 = J) you can transform this 4-digit to Apha with (presuming A3 has the 4-digit number):
=CHAR(MID(A3,1,1)+65)&CHAR(MID(A3,2,1)+65)&CHAR(MID(A3,3,1)+65)&CHAR(MID(A3,4,1)+65)

But do you only want alpha chars from A - J (0-9), or do you want all 26 possibilities?

A 4 digit alpha sequence with A-Z can be obtained from:
=CHAR(INT(RAND()*26+65))&CHAR(INT(RAND()*26+65))&CHAR(INT(RAND()*26+65))&CHAR(INT(RAND()*26+65))

You can change the "26" to the number of chars you need. The "65" represents the char of the "A".

Steve

Thanks Steve

I will try and get my pea sized brain around this properly

[steveh]

Morning all

As this password creator is going to be on an internal document I thought it would be neater to create a macro to refresh the result on the press of a button and then return the answer to the last blank row in column A. By using the macro recorder doing the screen refresh is easy but passing the information to the target cell has proved beyond me (as you know I am macro/code challenged!!)

This is what I have

Code: Select all

Sub RefreshPassword()
 Range("D5").Copy
 With Sheets("sheet1")
    Application.MaxChange = 0.001
    ActiveWorkbook.PrecisionAsDisplayed = False
    Calculate
    .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
With Selection.Font
.Name = "Arial"
.Size = 20
End With
    Application.CutCopyMode = False
End With
End Sub

But it stops on this line - .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues which I copied from another workbook example that copies data to the next available cell in column A.

[HansV]

The lines

Code: Select all

    Application.MaxChange = 0.001
    ActiveWorkbook.PrecisionAsDisplayed = False
    Calculate

clear the clipboard - if you single step through the code you can see the "marching ants" border around cell D5 being turned off. Do you really need these lines?

[steveh]

The lines

Code: Select all

    Application.MaxChange = 0.001
    ActiveWorkbook.PrecisionAsDisplayed = False
    Calculate

clear the clipboard - if you single step through the code you can see the "marching ants" border around cell D5 being turned off. Do you really need these lines?

Hi Hans

Not really, and I will try to remedy that, it was just how the macro recorder did it so I have blindly obeyed it!

Thanks

[HansV]

If you remove those three lines, the macro should work correctly.

[steveh]

If you remove those three lines, the macro should work correctly.

Thanks Hans

When I recorded the macro originally it was only to refresh the screen and those are the 3 lines it gave me so I assumed when trying to add the other operations that it would need to stay it but obviousely not, I guess that this would be because the 'onclick' or the 'copy' operation in itself makes the screen refresh, is that right?

[HansV]

I don't understand why you would want to recalculate the target sheet...

[steveh]

I don't understand why you would want to recalculate the target sheet...

Hi Hans

As they say, this was probably "Lost in Translation", or in other words I did not explain it properly

I first intended only to automate the refresh, I did this by open the macro recorder and the going to 'Tools', 'Options', 'Calculation' and click Calc Now, this gave me the 3 lines in question. When I then decided to try and elaborate it a bit more by copying and pasting the result to the target cell I assumed that these 3 lines of code would still be relevant as they were performing the 'F9' function for me.

When you advised me to remove them to make it work, which I did, I wondered what action was now making the sheet recalculate (F9) as the instruction was no longer there.

I hope that explains a little better

Thanks again

[HansV]

Thanks for the explanation.