Match Problem

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
656
Hi All

I'm having trouble getting this to work.:confused:

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


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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
619
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 21, 2013
Messages
656
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



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

Well-known Member
Joined
Aug 21, 2013
Messages
656
Hi

got there in the end (phew!!)

Many thanks for your help

cheers

Paul

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,626
Messages
5,838,438
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top