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 |
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. |
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 |
All times are GMT +1. The time now is 11:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com