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

 

[Alex Blakenburg]

If you are only looking in one column, something like this might work.
(I have assumed looking in Column C)

Private Sub cmdFINDVAL_Click()

Dim ws As Worksheet
Dim rng As Range
Dim txt As String
Dim returnTxt As String

Set ws = Worksheets("PROPHLINKS") 'change to your worksheet name
txt = Me.TextBox1.Text 'change to your textbox name

returnTxt = WorksheetFunction.XLookup("*" & txt & "*", ws.Range("C:C"), ws.Range("C:C"), "", 2)

If returnTxt <> "" Then
    Set rng = WorksheetFunction.XLookup("*" & txt & "*", ws.Range("C:C"), ws.Range("C:C"), "", 2)
    MsgBox "Found " & txt & " in cell " & rng.Address
Else
    MsgBox txt & " not found in " & ws.Name
End If

End Sub

 

[6StringJazzer]

you said you changed the Sheet name
to something else and it ran successfully ?

Yes, because I didn't have a sheet called PROPHLINKS so I used one I had. I was pretty sure the sheet name was not the issue.

 

[Skyybot]

Would not a match of 255 characters suffice? Would the following work?

Set rng = ws.Cells.FIND(What:=Left(txt,255), LookAt:=xlWhole)

 

[Alex Blakenburg]

Would not a match of 255 characters suffice? Would the following work?

Set rng = ws.Cells.FIND(What:=Left(txt,255), LookAt:=xlWhole)

If you are only taking the first 255 characters you need to change the xlWhole to xlPart.

 

[Skyybot]

If you are only taking the first 255 characters you need to change the xlWhole to xlPart.

Would you not want to match all 255 characters?

 

[Alex Blakenburg]

Would you not want to match all 255 characters?

The "find what" is 255 characters long what you are "looking in" is a longer string, so you need to find part of the longer string.

 

[6StringJazzer]

Would you not want to match all 255 characters?

xlWhole means look for the What string as a match for the entire cell. xlPart means look for the What string anywhere in the cell.

 

[Skyybot]

xlWhole means look for the What string as a match for the entire cell. xlPart means look for the What string anywhere in the cell.

Ah. I think I've been using it wrong this whole time.

 

[chazrab]

I don't know if everyone is getting this update, but the intention is a blanket reply to all. You've all been extremely helpful, and I very
much appreciate it. After a good nights sleep, I think I came up with the solution.

The objective was just to make any changes in any sheet cell from a userform textbox displaying that cells contents and save it back
to that cell on the sheet - straightforward enough.

in order to do that, I kept thinking that if I could get the cell address of that particular cell that's displayed in the textbox,,
I could use it by assigning it to a variable in the FIND method and letting FIND locate that cell address. Once that's done successfully.
changes to the cells text would be made, through the textbox and a Save button writes the updated changes to the correct cell back to the sheet.

The first thing I did was to write the cell address of each cell at the beginning of the cell for all cells. Since most situations like mine have
many many cells to work with, I needed a quick way to do this. So I used the Activecell with a small userform this way

Private Sub cmdINSERTADDRESS_Click()
If ActiveCell.Value <> "" Then
ActiveCell.Value = ActiveCell.Address(0, 0) + " " + ActiveCell.Value
Else
End If
End Sub

This puts the cell address without $'s at the beginning ot the cell text.
After making any changes to Textbox1 of the userform, I used this simple code to let FIND find the correct cell address

Dim c, e As Range
Set e = Worksheets("PROPHLINKS").Range("A1:T50")
   x = UserForm8.TextBox3.Value  ---> cell address of displayed sheet cell
With e
   Set c = .FIND(x, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
           If Not c Is Nothing Then
               MsgBox x & " " & "value found'" & "  " & c.Address
                c.Value = UserForm8.TextBox1.Value
           Else
               MsgBox "No value found"
          End If
          MsgBox "Note copied"
End With

Images of results below.
I only tested this a couple of times. The code seems to be correct it works for two cases. To me, then, it should work every time, unless there is some underlying bug that may pop up - I've had that happen before.

Once again, thanks for everyone's help. Hope all can see this reply.
cr

 

[6StringJazzer]

The objective was just to make any changes in any sheet cell from a userform textbox displaying that cells contents

If the textbox has text that was taken from a cell to begin with, wouldn't it be trivial to just save a reference to the cell at the time the textbox is loaded, and then save it back to that cell? When I first looked at your question I didn't reach back into what you were doing, I was just trying to figure out why you got an error on Find.

P.S. When I type FIND, the editor autocorrects to Find. I am still astonished that yours goes in the opposite direction.