Help With Find
I'm trying to find a row based on two criteria the first is a text box in a form called txton the value of which is held on column A. the second criteria is in the same row it finds an empty cell in column F. using the code below finds the first criteria no problem. does anyone have any idea how too expand this code to find the second criteria. Thanks in advance Dim FindString As String Dim rng As Range FindString = UserForm1.txtON.Value If Trim(FindString) < "" Then With Sheets("Re - Rostered Restdays").Range("A:A") Set rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Application.Goto rng, True Else MsgBox "Nothing found" End If End With End If -- paul_gu ------------------------------------------------------------------------ paul_gu's Profile: http://www.excelforum.com/member.php...o&userid=33007 View this thread: http://www.excelforum.com/showthread...hreadid=530570 |
Help With Find
Merge you code with the sample code in the help on FINDNEXT.
each time you find a cell in column A, then check if the corresponding cell in F is blank. If so, select the cell in A and quit. If not continue looking until all cells in A meeting the criteria have been checked. -- Regards, Tom Ogilvy "paul_gu" wrote: I'm trying to find a row based on two criteria the first is a text box in a form called txton the value of which is held on column A. the second criteria is in the same row it finds an empty cell in column F. using the code below finds the first criteria no problem. does anyone have any idea how too expand this code to find the second criteria. Thanks in advance Dim FindString As String Dim rng As Range FindString = UserForm1.txtON.Value If Trim(FindString) < "" Then With Sheets("Re - Rostered Restdays").Range("A:A") Set rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Application.Goto rng, True Else MsgBox "Nothing found" End If End With End If -- paul_gu ------------------------------------------------------------------------ paul_gu's Profile: http://www.excelforum.com/member.php...o&userid=33007 View this thread: http://www.excelforum.com/showthread...hreadid=530570 |
Help With Find
Sub test()
Dim FindString As String Dim rng As Range FindString = UserForm1.txtON.Value If Trim(FindString) < "" Then With Sheets("Re - Rostered Restdays").Range("A:A") Set rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Set rng = rng.Offset(0, 5) Do While rng.Value < "" Set rng = rng.Offset(1, 0) Loop rng.Select Else MsgBox "Nothing found" End If End With End If End Sub HTH -- AP "paul_gu" a écrit dans le message de ... I'm trying to find a row based on two criteria the first is a text box in a form called txton the value of which is held on column A. the second criteria is in the same row it finds an empty cell in column F. using the code below finds the first criteria no problem. does anyone have any idea how too expand this code to find the second criteria. Thanks in advance Dim FindString As String Dim rng As Range FindString = UserForm1.txtON.Value If Trim(FindString) < "" Then With Sheets("Re - Rostered Restdays").Range("A:A") Set rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Application.Goto rng, True Else MsgBox "Nothing found" End If End With End If -- paul_gu ------------------------------------------------------------------------ paul_gu's Profile: http://www.excelforum.com/member.php...o&userid=33007 View this thread: http://www.excelforum.com/showthread...hreadid=530570 |
Help With Find
tom thanks for taking a look i see what you mean i'm fairly new to vb and its taken me days to get this far could you help thank -- paul_g ----------------------------------------------------------------------- paul_gu's Profile: http://www.excelforum.com/member.php...fo&userid=3300 View this thread: http://www.excelforum.com/showthread.php?threadid=53057 |
Help With Find
thanks for taking a look but it fails on the rng.select lin -- paul_g ----------------------------------------------------------------------- paul_gu's Profile: http://www.excelforum.com/member.php...fo&userid=3300 View this thread: http://www.excelforum.com/showthread.php?threadid=53057 |
Help With Find
My humblest apologies that works perfectly many many thanks I’ve spen days working on tha -- paul_g ----------------------------------------------------------------------- paul_gu's Profile: http://www.excelforum.com/member.php...fo&userid=3300 View this thread: http://www.excelforum.com/showthread.php?threadid=53057 |
All times are GMT +1. The time now is 04:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com