How to edit data from a form in a sheet to another form with the Data using VBA?-CodePudding

ive been learning VBA and Excel in the past 2 weeks by my own during my free time, but sometimes we need some help, and currently ive no one to help besides the internet. So ive developed a Form(Sheet1) in a sheet using shapes and excel cells so the user could perform operations like insert,update, new register to the other sheet(Data) which is my Data Sheet or DataTable more specifically. But im struggling to get the update button to work. i could definitely use some help.

Heres my code:

Public Upda As String

Sub Search()
    '
    ' Search Macro
    '
    Dim Sheet As String, ans


    On Error GoTo Erro:

    Data.Activate


    Sheet = Data.Name

    ans = InputBox("Write down the ID", "Search")

    If ans = "" Then
    Sheet1.Activate
    Exit Sub
    End If
    Dim C
    With Worksheets(Data).Range("A:A")

    Set C = .Find(ans, LookIn:=xlValues, lookat:=xlWhole)

    If Not C Is Nothing Then

     C.Activate
     Sheet1.Cells(17, 9).Value = C.Value ' Id
     Sheet1.Cells(9, 4).Value = C.Offset(0, 1).Value ' Name
     ' here goes the other fields to be inserted
     Sheet1.Activate

     Upda = Sheet1.Cells(17, 9).Text
    Else

    Sheet1.Activate
    MsgBox "Insert a valid ID", vbCritical, "Search"


    End If

    End With

    Exit Sub
Erro:
        MsgBox "Something went wrong, contact the Adm!", vbCritical, "Erro"
  
End Sub

   
'Update macro need to get a fix
Sub Update()

'update macro

Dim Sheet As String


On Error GoTo Erro
If IsEmpty(Range("I17")) Or IsEmpty(Range("D9"))  Then ' there are more fields to validate
  MsgBox "All the fields must have a value", vbExclamation, "Upda"

If Upda = "" Then

 MsgBox "Please retry the search", vbExclamation, "Update"
Exit Sub
End If
Dim C
'
Data.Activate

Sheet = Data.Name

With Worksheets(Sheet).Range("A:A")

    Set C = .Find(Upda, LookIn:=xlValues, lookat:=xlWhole)

If Not C Is Nothing Then

    C.Activate

    ActiveCell.Value = Sheet1.Cells(17, 9).Text ' ID
    ActiveCell.Offset(0, 1).Value = Sheet1.Cells(9, 4).Text   ' Name
  'Update the table with the contents of the form1
    
    Sheet1.Activate
    Range("I6:J6").ClearContents
         ' remaining code to clear the contents of the form sheet1
    Upda = ""

    'Call clear
Else
MsgBox "ID number not found", vcCritical, "Update"

End If
End With


Exit Sub
Erro:
MsgBox "Something went wrong, contact the Adm!", vbCritical, "ERRO"
End Sub

Sub clear()
'
' clear Macro
'
    Range("I17").ClearContents
    ' remaining code to cleear the contents of the form sheet1
    Upda = ""
End Sub

Each one of those macros are associated with a Button(Shape), evrything is working besides the Update one.

Im getting the follow error which makes no sense to me

PS:if u need more information please let me know

CodePudding user response：

You are missing the End if statement for the first If in the below block of code:

If IsEmpty(Range("I17")) Or IsEmpty(Range("D9"))  Then ' there are more fields to validate
  MsgBox "All the fields must have a value", vbExclamation, "Upda"
End if 'Missing If in the original code

If Upda = "" Then

 MsgBox "Please retry the search", vbExclamation, "Update"
Exit Sub
End If