Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Macro - Find Activecell.value
Hello
I was trying to google it and checked on forums, but couldnt successfully found any solution to my problem. I have a list of names in coloum D of a worksheet called "Trial", I have another worksheets called "Usage" with this list. I need a macro which can search the name written in Trial worksheet in Usage and worksheet, and activate the found results in usage worksheet. I cannot use vlookup formulae here as "trial" sheet only has last names, which "usage" sheet has full names. Please help me, this is an urgent report which I need to make asap. Regards Gaurav
__________________
Regards Gaurav Last edited by Gaura215 : July 31st 12 at 08:51 AM Reason: Spell Mistake |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro - Find Activecell.value
On Tuesday, July 31, 2012 2:50:32 AM UTC-5, Gaura215 wrote:
Hello I was trying to google it and checked on forums, but couldnt successfully found any solution to my problem. I have a list of names in coloum D of a worksheet called "Trial", I have another worksheets called "Usage" with this list. I need a macro which can search the name written in Trial worksheet in Usage and worksheet, and activate the found results in usage worksheet. I cannot use vlookup formulae here as "trial" sheet only has last names, which "usage" sheet has full names. Please help me, this is an urgent report which I need to make asap. Regards Gaurav -- Gaura215 You can develop a macro using FIND. Try recording editfindvaluepart If all else fails send your file to dguillett1 @gamil.com with a complete explanation. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro - Find Activecell.value
Gaura215 wrote on 31-07-12 :
Hello I was trying to google it and checked on forums, but couldnt successfully found any solution to my problem. I have a list of names in coloum D of a worksheet called "Trial", I have another worksheets called "Usage" with this list. I need a macro which can search the name written in Trial worksheet in Usage and worksheet, and activate the found results in usage worksheet. I cannot use vlookup formulae here as "trial" sheet only has last names, which "usage" sheet has full names. Please help me, this is an urgent report which I need to make asap. Regards Gaurav ================================= Public Sub Trial_Usage() Dim RangeArr(1 To 2) As Range, CellFound As Range Dim i As Long, NameToFind As String ' Definition ------------------------- Set RangeArr(1) = [Trial!D1] Set RangeArr(2) = [Usage!D1] NameToFind = InputBox("Name to find") ' ------------------------------------ For i = 1 To 2 Set RangeArr(1) = RangeArr(1).Resize(RangeArr(1).End(xlDown).Row) Set RangeArr(2) = RangeArr(2).Resize(RangeArr(2).End(xlDown).Row) Next With RangeArr(1) Set CellFound = .Find(What:=NameToFind, _ MatchCase:=False, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ LookAt:=xlWhole, _ LookIn:=xlValues) If CellFound Is Nothing Then MsgBox NameToFind & " not found in " & RangeArr(1).Worksheet.Name Exit Sub End If End With With RangeArr(2) Set CellFound = .Find(What:=NameToFind, _ MatchCase:=False, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ LookAt:=xlPart, _ LookIn:=xlValues) If CellFound Is Nothing Then MsgBox NameToFind & " not found in " & RangeArr(2).Worksheet.Name Else RangeArr(2).Worksheet.Activate CellFound.Select End If End With End Sub ======================================= Let me know if it works. Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Find If The ActiveCell Is Protected | Excel Discussion (Misc queries) | |||
HOW TO FIND last cell from activecell? | Excel Programming | |||
Find ActiveCell.column | Excel Programming | |||
Use of Selection.Find & ActiveCell | Excel Programming | |||
selecting a cell relative to activecell in an excel macro | Excel Programming |