Match Problem

paulsolar

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

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
539
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
631
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
631
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,421
Messages
5,528,676
Members
409,829
Latest member
CFreeamaz

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top