Match Problem

[paulsolar]

Hi All

I'm having trouble getting this to work.

The first part of the code works fine but I cant get the match to work.

What I am trying to do is find the match in column A of sheet Plan Data which corresponds to the contents of textbox 10 in the userform.

What I then intend to do is in the row where the match is found in column B enter Cancelled, but I cant get the match to work. I've only ever used match once before and didn't have a lot of luck that time either. The msgbox is only there to help me.

Any ideas out there where I'm going wrong please

cheers

Paul

Private Sub UserForm_Initialize()
   Me.TextBox10.Text = CStr(ThisWorkbook.Sheets("plan status").range(ActiveCell.Address).Value)
   
End Sub


Private Sub TextBox11_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
On Error Resume Next
Me.TextBox11 = Format(TextBox11, "dd/mm/yyyy")


End Sub


Private Sub CommandButton10_Click()


Sheets("plan status").Unprotect Password:="password1"


Sheets("plan status").range("F" & ActiveCell.Row) = TextBox11.Value
Sheets("plan status").range("C" & ActiveCell.Row) = "Cancelled"
Sheets("plan status").range("D" & ActiveCell.Row) = "Cancelled"
Sheets("plan status").range("E" & ActiveCell.Row) = "Cancelled"


Sheets("plan status").Protect Password:="password1"


Findmatch


End Sub


Sub Findmatch()


Dim rng1 As range
Dim myFind
myFind = TextBox10.Value
Dim myrow As range


With Sheets("Plan Data").range("A4:A500")
myrow = .Find(What:=myFind, After:=ActiveCell, LookIn:=xlValue, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)Activecell.Row


MsgBox myrow & " row number"


End With


Unload UserForm3


End Sub

 

[C Moore]

WIth Find, the sheet your working with has to be active (can't just reference with 'With Sheets'). Before with sheets you'll have to do Sheets("Plan Data").Activate then move back when its done.

 

[paulsolar]

Hi

Many thanks for your reply.

I've added the activate and still no joy. The problem seem to be in the way I'm using the find.(highlighted in red)

As I said I'm not really familiar with the find function and cant seem to get my head around how it actually works at the moment. I've read what I can on the internet about it and it seems to me that I have it correct but it keeps bombing out on the at row

Any thoughts on how I should be writing it?

Cheers

Paul

Sub Findmatch()

Sheets("Plan Data").Activate

Dim rng1 As range
Dim myFind
myFind = TextBox10.Value

Dim myrow As range


With Sheets("Plan Data").range("A4:A500")

[COLOR=#ff0000]myrow = .Find(What:=myFind, After:=ActiveCell, LookIn:=xlValue, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row[/COLOR]

MsgBox myrow & " row number"

End With

Unload UserForm3

Sheets("Plan Status").Activate

End Sub

 

[paulsolar]

Hi

got there in the end (phew!!)

Many thanks for your help

cheers

Paul

Private Sub UserForm_Initialize()
   Me.TextBox10.Text = CStr(ThisWorkbook.Sheets("plan status").range(ActiveCell.Address).Value)
   
End Sub

Private Sub TextBox11_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
On Error Resume Next
Me.TextBox11 = Format(TextBox11, "dd/mm/yyyy")


End Sub


Private Sub CommandButton10_Click()

Sheets("plan status").Unprotect Password:="password1"

Sheets("plan status").range("F" & ActiveCell.Row) = TextBox11.Value
Sheets("plan status").range("C" & ActiveCell.Row) = "Cancelled"
Sheets("plan status").range("D" & ActiveCell.Row) = "Cancelled"
Sheets("plan status").range("E" & ActiveCell.Row) = "Cancelled"

Sheets("plan status").Protect Password:="password1"

Findmatch

End Sub
Sub Findmatch()

Sheets("Plan Data").Activate

Sheets("plan data").Unprotect Password:="password1"

Dim myFind
myFind = TextBox10.Value

Dim rngSearch As range, rngFound As range
Set rngSearch = range("A4:A500")
Set rngFound = rngSearch.Find(What:=myFind, LookIn:=xlValues, LookAt:=xlPart)
If rngFound Is Nothing Then
MsgBox "Not found"
Else

Sheets("Plan Data").range("B" & rngFound.Row & ":" & "D" & rngFound.Row) = "Cancelled"

End If

Sheets("plan data").Protect Password:="password1"

Unload UserForm3

Sheets("Plan Status").Activate

End Sub