[حل شده] جستجوی یک یا چند مورد در یک رنج

[ZAMEN58]

سلام خدمت دوستان عزیز
کد زیر یک مورد را در رنج جستجو کرده و آدرس سلول را نمایش میدهد
منبع

https://www.automateexcel.com/vba/find-replace/#Find_VBA_Example

Sub TestFind()Dim MyRange As Range Set MyRange = Sheets("Sheet1").UsedRange.Find("employee")If Not MyRange Is Nothing Then    MsgBox MyRange.Address    MsgBox MyRange.Column    MsgBox MyRange.RowElse    MsgBox "Not found"End IfEnd Sub 

و نمونه زیر یک یا چند مورد را در یک رنج جستجو می کند و اگر بیشتر از یک مورد بود آدرس همه انها را نمایش می دهد

Sub TestMultipleFinds()Dim MyRange As Range, OldRange As Range, FindStr As String 'Look for first instance of "‘Light & Heat"Set MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat") 'If not found then exitIf MyRange Is Nothing Then Exit Sub 'Display first address foundMsgBox MyRange.Address 'Make a copy of the range objectSet OldRange = MyRange 'Add the address to the string delimiting with a "|" characterFindStr = FindStr & "|" & MyRange.Address 'Iterate through the range looking for other instancesDo    'Search for ‘Light & Heat’ using the previous found address as the After parameter       Set MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", After:=Range(OldRange.Address))     'If the address has already been found then exit the do loop – this stops continuous looping    If InStr(FindStr, MyRange.Address) Then Exit Do        'Display latest found address    MsgBox MyRange.Address     'Add the latest address to the string of addresses    FindStr = FindStr & "|" & MyRange.Address     'make a copy of the current range     Set OldRange = MyRangeLoopEnd Sub 

کد زیر هم با lookat می باشد

Sub TestLookAt()Dim MyRange As RangeSet MyRange = Sheets("Sheet1").UsedRange.Find("light", Lookat:=xlPart)If Not MyRange Is Nothing Then    MsgBox MyRange.Address  Else    MsgBox "Not found"  End IfEnd Sub