Insert values based on selection (2003)

Topic

New Reply

I am trying to fill in a series of cells (10×20) 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.

Reply | Quote

Replies

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

Reply | Quote

Outstanding!

Thanks Hans.

Reply | Quote

You could use the Before Double Click event.

Reply | Quote

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.

Reply | Quote