Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find. On other sheet
Hi, I have this working - Dim rngFound As Range TheCol = "N" With Range(theCol & ":" & theCol) Set rngFound = .Find(What:=mt, after:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) If Not rngFound Is Nothing Then r = rngFound.Row End If End With It Finds any mt in Column N. But only in the sheet that's open. I can't quite make it work with a specific Sheet by name. I think it goes into the Range line. Could someone show me the syntax, please? Thanks - Kirk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find. On other sheet
You don't need the after and the other parameters I eliminated.
Dim rngFound As Range TheCol = "N" With Sheets("Sheet1").Range(theCol & ":" & theCol) Set rngFound = .Find(What:=mt, _ LookIn:=xlValues, _ LookAt:=xlPart) If Not rngFound Is Nothing Then r = rngFound.Row End If End With "kirkm" wrote: Hi, I have this working - Dim rngFound As Range TheCol = "N" With Range(theCol & ":" & theCol) Set rngFound = .Find(What:=mt, after:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) If Not rngFound Is Nothing Then r = rngFound.Row End If End With It Finds any mt in Column N. But only in the sheet that's open. I can't quite make it work with a specific Sheet by name. I think it goes into the Range line. Could someone show me the syntax, please? Thanks - Kirk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find. On other sheet
On Mar 21, 3:57*am, kirkm wrote:
Hi, I have this working - Dim rngFound As Range TheCol = "N" With Range(theCol & ":" & theCol) * * Set rngFound = .Find(What:=mt, after:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) * * If Not rngFound Is Nothing Then * * r = rngFound.Row * * End If End With It Finds any mt in Column N. But only in the sheet that's open. I can't quite make it work with a specific Sheet by name. I think it goes into the Range line. Could someone show me the syntax, please? Thanks - Kirk Kirk, Here is a sample of some code that you should be able to reference for your syntax. Best, Matt Herbert Sub CustomFind() Dim rngFound As Range Dim strCol As String Dim wks As Worksheet Dim strMt As String Dim rngLastCell As Range Dim lngR As Long strCol = "N" strMt = "Kirk" Set wks = Worksheets("Sheet2") Set rngLastCell = Columns(strCol) Set rngLastCell = rngLastCell.Cells(rngLastCell.Cells.Count) With wks.Columns(strCol) Set rngFound = .Find(What:=strMt, after:=rngLastCell, _ LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, _ searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) If Not rngFound Is Nothing Then lngR = rngFound.Row End If End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find. On other sheet
I don't like using after unless you have to . The problem is when it gets to
the end of the range it goes back to the beginning. If you start with a match in the cell specified by the AFTER parameter the code will end at the same cell and form a match. For eample A1 = 5 A2 = 6 A3 = 7 set c = Range("A1:A3").Find(what:=6, after:=Range("A2")) c will return A2, not nothing. So you have to test for nothing and address not equal to A2. Like this set c = Range("A1:A3").Find(what:=6, after:=Range("A2")) if not c is nothing and c.address < "A2" " wrote: On Mar 21, 3:57 am, kirkm wrote: Hi, I have this working - Dim rngFound As Range TheCol = "N" With Range(theCol & ":" & theCol) Set rngFound = .Find(What:=mt, after:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) If Not rngFound Is Nothing Then r = rngFound.Row End If End With It Finds any mt in Column N. But only in the sheet that's open. I can't quite make it work with a specific Sheet by name. I think it goes into the Range line. Could someone show me the syntax, please? Thanks - Kirk Kirk, Here is a sample of some code that you should be able to reference for your syntax. Best, Matt Herbert Sub CustomFind() Dim rngFound As Range Dim strCol As String Dim wks As Worksheet Dim strMt As String Dim rngLastCell As Range Dim lngR As Long strCol = "N" strMt = "Kirk" Set wks = Worksheets("Sheet2") Set rngLastCell = Columns(strCol) Set rngLastCell = rngLastCell.Cells(rngLastCell.Cells.Count) With wks.Columns(strCol) Set rngFound = .Find(What:=strMt, after:=rngLastCell, _ LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, _ searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) If Not rngFound Is Nothing Then lngR = rngFound.Row End If End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find. On other sheet
Thanks to you both, you've given me lots of ideas and I have got it working nicely, including a loop that looks for more than one instance by updating the range values. I just wonder - what if you wanted to check 2 adjacent columns ? This fails (so obvously isn't right but I can't think of an alternative, apart from searching twice) theCol = "S2:T100" mt = "RowSRowT" Do With Sheets("Sheet1").Range(theCol) Set rngFound = .Find(What:=mt, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Not rngFound Is Nothing Then r = rngFound.Row End If End With I've set up this test condition Cells(10,"S") = "RowS" Cells(10,"T") = "RowT" Thanks - Kirk |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find. On other sheet
You are looking for two different string of text. What you did won't work
SearchStr = array("RowS",RowT") theCol = "S2:T100" for each MyStr in SearchStr With Sheets("Sheet1").Range(theCol) Set rngFound = .Find(What:=MyStr, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Not rngFound Is Nothing Then r = rngFound.Row End If End With Next MyStr "kirkm" wrote: Thanks to you both, you've given me lots of ideas and I have got it working nicely, including a loop that looks for more than one instance by updating the range values. I just wonder - what if you wanted to check 2 adjacent columns ? This fails (so obvously isn't right but I can't think of an alternative, apart from searching twice) theCol = "S2:T100" mt = "RowSRowT" Do With Sheets("Sheet1").Range(theCol) Set rngFound = .Find(What:=mt, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Not rngFound Is Nothing Then r = rngFound.Row End If End With I've set up this test condition Cells(10,"S") = "RowS" Cells(10,"T") = "RowT" Thanks - Kirk |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find. On other sheet
On Sat, 21 Mar 2009 20:23:01 -0700, Joel
wrote: You are looking for two different string of text. What you did won't work Understood. All sorted now, many thanks. Cheers - Kirk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to find data from one sheet & copy in another sheet | Excel Programming | |||
Find value in sheet 1 and copy matching row from sheet 2 | Excel Programming | |||
how can find sheet on workbook have so many sheet ? | Excel Worksheet Functions | |||
Find value from sheet 1 on sheet 2 and copy to an offset from there | Excel Programming | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming |