Problem using FIND method with a variable to update changes to a sheet cell

[chazrab]

I'm getting the old 'type mismatch' error at this line:

Private Sub cmdFINDVAL_Click()
Dim ws As Worksheet
Dim rng As Range
Dim txt As String
Set ws = Worksheets("PROPHLINKS") 'change to your worksheet name
txt = Me.TextBox1.Text 'change to your textbox name
Set rng = ws.Cells.FIND(What:=txt)  ---> type mismatch error at this line
If Not rng Is Nothing Then
    MsgBox "Found " & txt & " in cell " & rng.Address
Else
    MsgBox txt & " not found in " & ws.Name
End If

the variable txt is a large block of text in a Userform textbox. It is exactly the same as the sheet cell value.
the error and the variable text images below. I'm using the FIND method to find this same value in the sheet in order to update
the cell with changes made from the textbox. Standard process, yet not working.

Any idea why this is not working ?

Thanks for anyone's help.
cr

 

[Skyybot]

What is the Value of txt when the Sub errors?

 

[6StringJazzer]

When I run your code, changing only the sheet name, it works. I tested for both a string that is found and one that is not found.

It's odd that FIND is in all caps. It should be autocased to Find. I wonder if that's a clue.

 

[chazrab]

What is the Value of txt when the Sub errors?

What is the Value of txt when the Sub errors?

Hi Skyybot - txt - Textbox1.Text or Textbox1.value - that is, the entire text. Image below. If the cell contents match Textbox1's contents,
FIND should find that exact cell, should it not? I can't see why the amount of text or spacing would make any difference. The goal is
to get to that exact same cell. To me, there's no reason why this code should not work. Just extra comments. Let me know what you think.
Thx for help. cr

 

[Skyybot]

I wanted to know the value of the txt variable in your code when you Step Through it because it sounds like there may be a mismatch between the data type that "What:=" argument is looking for and what you're giving it.

 

[chazrab]

When I run your code, changing only the sheet name, it works. I tested for both a string that is found and one that is not found.

It's odd that FIND is in all caps. It should be autocased to Find. I wonder if that's a clue.

Hi 6stringjazzer - thx for helping with this - when I tried to change FIND to FInd, Excel VBA automatically recapitalizes it. I have changed
the sheet name PROPHLINKS t o Sheet4 with still the same 'type mismatch' error - yet, but you said you changed the Sheet name
to something else and it ran successfully ?

That's pretty strange to me - why would VBA not like a sheet name if its a legal and correct name? And why would you get it to
run for you and I still get the same issue here ? All I'm wanting to do is use the Find method to update changes in any cell from a
userform textbox, as I mentioned earlier. Image of changed sheet name below.

Thanks gain for helping. This should not be this difficult - I've written many apps using different versions of Find,( FIND) and they all
worked great - except for this one.

 

[Alex Blakenburg]

Despite reading somewhere else that Find can handle a much longer string I have also read that the Maximum no of characters it can handle is 255.
I have tested it and that seems to be the case.

So if your lookup text is > 255 which seems to be likely from your image, the mismatch type 13 error is to be expected.

 

[chazrab]

Despite reading somewhere else that Find can handle a much longer string I have also read that the Maximum no of characters it can handle is 255.
I have tested it and that seems to be the case.

So if your lookup text is > 255 which seems to be likely from you image, the mismatch type 13 error is to be expected.

Hi Alex - as I suspected. I'm using the entire textbox text values to locate the correct cell to post to - and there is way more than 255 chracters in some cells on the sheet.
Is there any way to get the cell absolute address(w/out $ signs) of the correct cell Textbox1's value is taken from
and then use FIND to find that cell address and post changes to that cell ?

To me, if that's possible, that seems the simplest way to do this - if I could just figure out how to identify the cell address
Textbox1's value is taken and displayed from ?
- i.e, B1, D14, G54 ?
Then

Dim txt as string
txt = "B1"  'this correct cell address has to be obtained in some way. 
Set rng = ws.Cells.FIND(What:=txt)

Thx for helping. Sorry for being long winded about this. Just want to get it work - if can be done.
cr

 

[Alex Blakenburg]

Is there any chance that you only need to look for the text in one or a limited number of columns ?
If in one column I think XLookup will work. If numerous columns we need details of the range to look in and then we would need to loop through an array.

 

[Skyybot]

rCell = Me.TextBox#.LinkedCell   'Change # to whichever TextBox you're using.