How Good Are You?

[jamesmev]

Solve this riddle if you would...

Within A Table:

Product Suggested Price Number Per Litter
Dog 9.99 9
Cat 5.99 5
Bat 2.00 3



User clicks: "DOG" within the table

That then places

Litter Number in Cell B2 (9)
and Suggested Price in Cell B3 (9.99)


Any Advice?

Thanks in advance.

 

[Joe4]

You already have a table, so use VLOOKUP.
See: https://www.techonthenet.com/excel/formulas/vlookup.php

 

[jamesmev]

The action takes place when the cell is clicked. That is the difficulty.
I have"

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Cancel = True
Target.Copy Destination:=Sheets("Sheet1").Range("C9")
End If
End Sub"

But it only selects one selection not all the selections in that row and to the correct ranges.

 

[Joe4]

What is the exact range that this table of yours is in?

 

[Rick Rothstein]

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Cancel = True
Target.Copy Destination:=Sheets("Sheet1").Range("C9")
End If
End Sub"

But it only selects one selection not all the selections in that row and to the correct ranges.

Actually, your code does not "select" any cells, it assigns the value from the cell double-clicked on to cell C9.

In addition to the question Joe4 asked in Message #4 , what is not clear to me is if there are more than one Dog in the list, how should all the litter numbers be returned to you... do you want each litter number in a separate cell (if so, which cells)?... or do you want them all placed in cell C9 perhaps with a comma space between them?