Help With Find

[paul_gu]

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

[Tom Ogilvy]

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
>
>

[paul_gu]

tom thanks for taking a look i see what you mean i'm fairly new to vba and its taken me days to get this far could you help

thanks

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[paul_gu]

thanks for taking a look but it fails on the rng.select line

[paul_gu]

My humblest apologies that works perfectly many many thanks I’ve spent days working on that