Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Ohio, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert values based on selection (2003)

    I am trying to fill in a series of cells (10x20) with values from a range located on another sheet.
    When the user selects a cell I would like to take the value of that cell and use it to look up the values that are to be filled in.

    Example:

    User clicks on cell C4. It has an entered value of 5200.
    Cells B17:K36 would then be filled in with values taken from the 20 rows starting where column a has a value of 5200.

    Different cells for selection would have different values that would correspond to the value of column A in the starting row for data.
    The data is always 20 rows down and 10 columns across.

    See attached spreadsheet for clarification (I hope)

    I'm a little out of my normal VBA playing with this one...any help is appreciated.
    Pat Russell
    Process Automation Engineer

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert values based on selection (2003)

    Don't you think that just clicking in a cell is a bit dangerous - if the user accidentally clicks in another cell in the range B3:K12, or if he/she uses the arrow keys, the fill range would be updated. I'd put a command button next to B3:K12, so that the user can select a cell and then click the button.

    The macro executed by the button could look like this (if I interpret what you want correctly):

    Sub Fillem()
    Dim oStart As Range
    If Intersect(ActiveCell, Range("B3:K12")) Is Nothing Then
    MsgBox "Please select a cell in B3:K12.", vbExclamation
    Exit Sub
    End If
    Set oStart = Range(Range("A46"), Range("A46").End(xlDown)).Find( _
    What:=ActiveCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If oStart Is Nothing Then
    MsgBox "Value not found.", vbExclamation
    Exit Sub
    End If
    oStart.Offset(0, 1).Resize(20, 10).Copy
    Range("B17").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    End Sub

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Ohio, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert values based on selection (2003)

    Outstanding!

    Thanks Hans.
    Pat Russell
    Process Automation Engineer

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert values based on selection (2003)

    You could use the Before Double Click event.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert values based on selection (2003)

    True, I thought of that. But users expect that double-clicking a cell enters edit mode. A command button seemed more intuitive to me. But if the OP prefers double-click, it woild be a good solution.

Posting Permissions

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