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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
911
Office Version
  1. 365
Platform
  1. Windows
I'm getting the old 'type mismatch' error at this line:
Code:
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
 

Attachments

  • TYPE MISMATH ERROR AT THIS LINE.png
    TYPE MISMATH ERROR AT THIS LINE.png
    45.6 KB · Views: 9
  • txt = Textbox1.text.  Find method should Find any parital value o this Textbox in the sheet ce...png
    txt = Textbox1.text. Find method should Find any parital value o this Textbox in the sheet ce...png
    86.5 KB · Views: 9

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
What is the Value of txt when the Sub errors?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 

Attachments

  • CHANGED TO SHEET4.  SAME RESULT OVER HERE.png
    CHANGED TO SHEET4. SAME RESULT OVER HERE.png
    32.7 KB · Views: 3
Upvote 0
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.
 
Last edited:
Upvote 0
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
Code:
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
 
Upvote 0
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.
 
Upvote 0
VBA Code:
rCell = Me.TextBox#.LinkedCell   'Change # to whichever TextBox you're using.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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