Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search range, if exist, copy cell
Hi, Wondering if someone could help me out. Eg below. 1.) I want to define a value. Ideally click on the cell that contain this value and have this read as the value. i.e click cell (A1) - an read 'apple' as the value. 2.) Search a range (A1:A5) for this value. Where this value exists, a that row number, select another value from a different column (C?) an paste to area within worksheet (E) 3.) Increment the rows where the pasting is going to build a list o values. Any help on the syntax would be greatly appreciated. Many thanks. Marcus Eg: A B C D E 1 apple 1 1 2 apple 1 1 3 pear 2 1 4 banana 3 5 apple -- marcus7 ----------------------------------------------------------------------- marcus76's Profile: http://www.excelforum.com/member.php...fo&userid=1518 View this thread: http://www.excelforum.com/showthread.php?threadid=26814 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
search range, if exist, copy cell
How about with a right-click on the cell?
The following code relies on two named ranges; SearchRange for the range you are looking in for the match (A1:A5, in your message) and ValueList (the top cell - only the one cell - where you want your value list to begin). It uses the BeforeDoubleClick event to activate it and checks for a few possible error conditions. The Cancel=True at the end will bypass the normal response to a right-click (e.g. bringing up the context menu). Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim CellVal As Variant, MatchRow As Integer, MatchFound As Boolean If Selection Is Nothing Then MsgBox "Nothing Selected", vbExclamation Exit Sub End If CellVal = Target.Value If CellVal = "" Then MsgBox "Empty Cell - no value to match!", vbExclamation Exit Sub End If MatchFound = False On Error GoTo Oops MatchRow = WorksheetFunction.Match(CellVal, Range ("SearchRange"), 0) MatchFound = True Oops: On Error GoTo 0 If Not (MatchFound) Then MsgBox "No Match!", vbExclamation Exit Sub End If CellVal = Range("SearchRange").Offset(MatchRow - 1, 2).Range("A1").Value With Range("ValueList") MatchRow = .CurrentRegion.Rows.Count If .Range("A1").Value = "" Then MatchRow = 0 .Offset(MatchRow, 0).Value = CellVal End With Cancel = True End Sub -----Original Message----- Hi, Wondering if someone could help me out. Eg below. 1.) I want to define a value. Ideally click on the cell that contains this value and have this read as the value. i.e click cell (A1) - and read 'apple' as the value. 2.) Search a range (A1:A5) for this value. Where this value exists, at that row number, select another value from a different column (C?) and paste to area within worksheet (E) 3.) Increment the rows where the pasting is going to build a list of values. Any help on the syntax would be greatly appreciated. Many thanks. Marcus Eg: A B C D E 1 apple 1 1 2 apple 1 1 3 pear 2 1 4 banana 3 5 apple 1 -- marcus76 ---------------------------------------------------------- -------------- marcus76's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=15183 View this thread: http://www.excelforum.com/showthread...hreadid=268146 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
search range, if exist, copy cell
Uh, I meant of course the "SheetBeforeRightClick" event!
(Could also do this with a double-click) -----Original Message----- How about with a right-click on the cell? The following code relies on two named ranges; SearchRange for the range you are looking in for the match (A1:A5, in your message) and ValueList (the top cell - only the one cell - where you want your value list to begin). It uses the BeforeDoubleClick event to activate it and checks for a few possible error conditions. The Cancel=True at the end will bypass the normal response to a right-click (e.g. bringing up the context menu). Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim CellVal As Variant, MatchRow As Integer, MatchFound As Boolean If Selection Is Nothing Then MsgBox "Nothing Selected", vbExclamation Exit Sub End If CellVal = Target.Value If CellVal = "" Then MsgBox "Empty Cell - no value to match!", vbExclamation Exit Sub End If MatchFound = False On Error GoTo Oops MatchRow = WorksheetFunction.Match(CellVal, Range ("SearchRange"), 0) MatchFound = True Oops: On Error GoTo 0 If Not (MatchFound) Then MsgBox "No Match!", vbExclamation Exit Sub End If CellVal = Range("SearchRange").Offset(MatchRow - 1, 2).Range("A1").Value With Range("ValueList") MatchRow = .CurrentRegion.Rows.Count If .Range("A1").Value = "" Then MatchRow = 0 .Offset(MatchRow, 0).Value = CellVal End With Cancel = True End Sub -----Original Message----- Hi, Wondering if someone could help me out. Eg below. 1.) I want to define a value. Ideally click on the cell that contains this value and have this read as the value. i.e click cell (A1) - and read 'apple' as the value. 2.) Search a range (A1:A5) for this value. Where this value exists, at that row number, select another value from a different column (C?) and paste to area within worksheet (E) 3.) Increment the rows where the pasting is going to build a list of values. Any help on the syntax would be greatly appreciated. Many thanks. Marcus Eg: A B C D E 1 apple 1 1 2 apple 1 1 3 pear 2 1 4 banana 3 5 apple 1 -- marcus76 --------------------------------------------------------- - -------------- marcus76's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=15183 View this thread: http://www.excelforum.com/showthread...hreadid=268146 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search if cell contents exist in other cells | Excel Worksheet Functions | |||
Does specific value exist in a range | Excel Worksheet Functions | |||
range name does not exist | Excel Discussion (Misc queries) | |||
how to copy cells which gaps exist between? | Excel Discussion (Misc queries) | |||
How to search and copy cell content to another sheet? | Excel Worksheet Functions |