Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Formula help needed

    Hi all,

    I would like the data I enter in H2 to be entered in tne first available
    cell in coumn D.
    Is there a formula for this?
    This so I can hide columns A and D.

    Thanks,
    Albert
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't understand what you want exactly but based on what you seem to be asking you need a macro. Add this macro to the worksheet and whenever you change H2, its contents will go into the next cell in Column d...

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("H2")) Is Nothing Then
        Application.EnableEvents = False
        Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = Range("H2")
        Application.EnableEvents = True
      End If
    End Sub
    Note that this will disable UNDO in the worksheet

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    abaas (2011-12-05)

  4. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Hi Steve,


    Thanks for the reply.
    Added the macro to the worksheet.
    This is what I had in mind.
    After adding the data in H2 the first empty cell is filled.
    But when I add new data the next blank cell is not selected, instead it overwrites the previous entered data.

    Thanks,
    Albert
    Last edited by abaas; 2011-12-05 at 18:56.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That is not the behaviour I see. When I add the code to your workbook and enter a value in H2, it is entered in D11. When the value in H2 is changed it is entered into D12, when H2 is changed again, it is entered into D13 etc. The code does not clear H2 since you are just overwriting the value anyway.

    This is exacctly what you asked for "I would like the data I enter in H2 to be entered in tne first available cell in coumn D. "

    Perhaps if you detailed exactly what you want the code to do...

    Steve

  6. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Don't know what's wrong here on my side.
    Could you sent me a copy of the of the one that works for you?
    Appreciate it!

    Thanks,
    Albert
    Last edited by abaas; 2011-12-05 at 21:56.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here it is

    Steve
    Attached Files Attached Files

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    abaas (2011-12-06)

  9. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts
    That works.
    Thank you Steve.

Posting Permissions

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