Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Quick and probably easy question.

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    London, Ontario, Canada
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Heya gang,

    I know I've surpassed my newbie questions for the day but maybe you could help with this real quick.

    Got a user form (from Mr.Excell's example in the tips of the week from long ago, this is the "OK" button.)

    See where it says Range("A2")?
    Is there any way to put a variable in there that'll let the info be printed on the next available line?

    Private Sub CommandButton1_Click()
    Worksheets("Sheet3").Range("A2") = UserForm1.ComboBox1.Value
    UserForm1.Hide
    End Sub






  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi SuperSean,

    Just change the line to:

    Worksheets("Sheet3").[a65536].End(xlUp) = UserForm1.ComboBox1.Value
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    London, Ontario, Canada
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks a bunch!

    I have one question left about this though.
    I'm just trying to figure out what I'm telling excell.

    Worksheets("Sheet3").[a65536].End(xlUp) = UserForm1.ComboBox1.Value

    [a65536] - What's this and where did it come from????

    I understand the rest, thanks for the help!


  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    London, Ontario, Canada
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hrmnnn,

    Seems I answered my own question here. The a65536 is a cell reference way way down at the bottom of the sheet, then with end(xlup) we go to the nearest available line (from bottom) my problem now is that it replaces the text in that row instead of moving down one each time. What I need is for data to appear in A2 the first time I choose, A3 the second time ect..

    Any ideas?

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try going from the current line DOWN. Should be much faster than starting at the bottom and going up.

    Worksheets("Sheet3").[A1].End(xlDown) = UserForm1.ComboBox1.Value
    'move to the next cell
    Activecell.offsett(1,0).select

    'continue from here


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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