Help with An Activecell Error

[Erica]

I am getting an error when I try to run a macro to create random alpha-numeric codes in a spread sheet.



Sub NewPass()
Do
If ActiveCell.Offset(0, -8) <> "" Then
ActiveCell.Formula = Chr(Int((26 * Rnd) + 97)) & Format(Int(100000 * Rnd), "00000")
ActiveCell.Offset(1, 0).Select
Else
Exit Sub
End If
Loop
End Sub

This is the code and the If ActiveCell.Offset(0, -8) <> "" Then Line has an error (Runtime Error 1004)(Application-defined or object defined error). Someone from this board wrote this code for me and I don't know how to fix it. I am trying to create a password with six characters, a letter first and then five numbers in column I for a name in column F or a Date in Column A (I don't know if it makes a difference).

Can anyone help me with this?
:confused: :confused: :confused:

[CBrine]

My guess is this

ActiveCell.Formula = Chr(Int((26 * Rnd) + 97)) & Format(Int(100000 * Rnd), "00000")
Should be

ActiveCell.value = Chr(Int((26 * Rnd) + 97)) & Format(Int(100000 * Rnd), "00000")

I think you need to put a formula in when you use the .formula extension.
Have not tested it. HTH

[tinyjack]

How are you running the code?

TJ

[Erica]

TJ,

I am going to Tools - Marco - Marcos and then clicking run. This code worked before, but it is not working now.

[sebastienm]

Hi,
the code basically says:
- Start at the Activecell (the cell that is currently selected)
- if the cell located 8 cells on the left of Activecell on the same row is different than "" then create a random number in Activecell. Else stop execution.
- go to the cell just under Activecell. Select it (it becomes ActiveCell) and do the above step again.

So, I suppose you have selected a cell in column A,B,..., or H, therefore, when the code tries to go 8 cells on the left (.Offset(0, -8), ie before column A, it fails.

What are you trying to do exactly?

Regards,
Sebastien

[Erica]

Sebastienm,

Thank you. I had the active cell as A1 instead of I1. It worked.

[mdmackillop]

Hi Erica,
I made up a KB item here to do this sort of thing
http://www.vbaexpress.com/kb/getarticle.php?kb_id=110
MD

[Erica]

MD,

Thank you. You were actually the one who did the original one for me. I had the active cell as 1A instead of 1I.