0

I need this code for modifying some info of a client. I found useful to add an example of my formulary, so this is how the info appears when I try to modify.

I want to take the information of the new text inserted and put it on the cells of this client who has an ID, that's why I need the number of the row where this exact ID is, to modify those cells.

I don't know how to use VBA and my teacher thinks we should (I don't agree because I'm studying mechanics but OK). Anyway, I've tried all the things you have said to me and still have the '91 error1'.

Private Sub Modify_Click()

Dim FILA As Range
Dim codigo As Integer
Dim Linea As Long

codigo = Codigo_txt.Value
valor_buscado = Me.Codigo_txt
Set FILA = Sheets("Clientes").Range("A:A").Find(valor_buscado, lookat:=xlWhole)
Linea = FILA.Row

Range("B" & Linea).Value = Me.txt_name.Value
Range("C" & Linea).Value = Me.txt_surname.Value
Range("D" & Linea).Value = Me.txt_ID.Value
Range("E" & line).Value = Me.txt_telf.Value
Range("F" & Linea).Value = Me.txt_adress.Value
Range("G" & Linea).Value = Me.txt_CP.Value
Range("H" & Linea).Value = Me.txt_town.Value
Range("I" & Linea).Value = Me.txt_email.Value


End Sub

As I said, I want to get the number of the row with .Row, but it doesn't work.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Itziar Borràs
  • 3
  • 2
  • `.Row` does work. Have you checked that the `Find` did return something? Is `FILA` set to a cell containing the searched text? Also, you should declare `FILA` as `Range`, not as `Object`. – FunThomas Apr 13 '23 at 12:59
  • Save the row number to a hidden label when you fill the text boxes then you can post back the amended values to the sheet without searching. – CDP1802 Apr 13 '23 at 13:07
  • Change `line` to a `Long` - integer only goes up to row 32,767. Beyond that you'll get an overflow error. – Darren Bartrup-Cook Apr 13 '23 at 14:14

1 Answers1

0

you are looking for a value (valor_buscado) and you are looking for it in sheet ("Clientes") in Range("N°").

As the error is located on line 6 here are some explanations :

  1. The value you are looking for is not in the range consequently FILA = Nothing and linea is in ERROR.

  2. The value exists in the range but the row number of its location is > 32 768 which is the max for integer (you put dim linea as integer. Put Long instead) consequently linea is in ERROR.

If this does not help please find below a method for searching values in Range.

 Sub MultipleSearch()

' Get name to search
 Dim name As String

name = "Antonio" ' the string you are looking for 

 ' Get search range
 Dim rgSearch As Range
Set rgSearch = Range("PEOPLE")

Dim cell As Range
Set cell = rgSearch.Find(name)

'Set cell = rgSearch.Find(Item, LookAt:=xlWhole) xlWhole : IF Cell contains Antonio , xlPart (default value) : If Cell contains "An" and we are looking for "Antonio" the search will return TRUE that is why we want xlWhole.

' If not found then exit
If cell Is Nothing Then
Debug.Print "Not found" ' we look in the whole range and we did not find the value
 Sub
End If

 ' Store first cell address
Dim firstCellAddress As String
firstCellAddress = cell.Address
    
' Find all cells containing Antonio
Do
MsgBox ("Found: " & cell.Address)
Set cell = rgSearch.FindNext(cell)
 Loop While firstCellAddress <> cell.Address

End Sub
FunThomas
  • 23,043
  • 3
  • 18
  • 34
LE__Visionnaire
  • 41
  • 5