VBA Trouble with finding row number

Question

For whatever reason the following isn't working for me. Comes us with subscript out of range What I'm after is for vba to find the value from textbox2 in row A of worksheet "Log" and return row number then count 8 to right and set value of the cell of that row in 8th column as textbox1 value..

Value is definitely in column A but it doesn't seem to find it.

Private Sub CommandButton1_Click()
Dim answer As Integer
Dim lrow As String

answer = MsgBox("Do you wish to amend this absence?", vbYesNo, "Proceed?")
If answer = vbYes Then
lrow = Sheets("Log").Range("A:A").Find(What:=UserForm3.TextBox2.Value, LookIn:=x1Values)
Sheets("Log").Range(lrow, 8).Value = UserForm3.TextBox1.Value



Else
End If
End Sub

Thanks for your help

Answer 1

Few mistakes:

• You want to return the row of the returned range object, so the code would look like:

  lrow = Sheets("Log").Range("A:A").Find(What:=UserForm3.TextBox2.Value, LookIn:=xlValues).Row
  

• Since lrow is a number we want to use Dim lrow as Long

• Now we can use that in another cell reference:

  Sheets("Log").Cells(lrow, 8).Value = UserForm3.TextBox1.Value
  

Do you notice how you also made a small mistake using x1Values instead of xlValues > L instead of 1! And do you also see how I changed .Range(lrow, 8) into .Cells(lrow, 8)?

---

To add, you might also want to consider to use LookAt:=xlWhole if you are looking for exact matches.