Consulting

Results 1 to 8 of 8

Thread: Help with An Activecell Error

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location

    Help with An Activecell Error

    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?

  2. #2
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    389
    Location
    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
    The most difficult errors to resolve are the one's you know you didn't make.


  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Posts
    14
    Location
    How are you running the code?

    TJ
    Oh dear, I need a beer

  4. #4
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location
    TJ,

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

  5. #5
    VBAX Regular
    Joined
    Jun 2004
    Location
    USA
    Posts
    10
    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
    Seb

  6. #6
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location
    Sebastienm,

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

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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

  8. #8
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •