Runtime Error 1004

[chipsworld]

Trying to pass data from a form to a sheet...getting runtime error 1004 and have no idea why...

Can someone take a look and tell me here I am going wrong?

Private Sub cmdupdate_Click()
Dim wsadd As Worksheet

Set wsadd = Sheets("Historical")
    
            Dim Rw As Integer
            
    Rw = Sheets("historical").Range("B:B").Find(Me.txtnam.Value, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious).Row
    
        With wsadd
        
             Sheets("historical").Range("W", Rw).Value = Me.cmbstate.Value
             Sheets("historical").Range("X", Rw).Value = Me.txtlstfor.Value
             Sheets("historical").Range("T", Rw).Value = Me.txtunit.Value
             Sheets("historical").Range("V", Rw).Value = Me.cmbrefrad.Value
             Sheets("historical").Range("U", Rw).Value = Me.txtuic.Value
        
        End With
    
    


End Sub

 

[Joe4]

When I see this error with VBA code that uses "Find", it is often because it cannot find the value you are looking for.

 

[dmt32]

Hi,
untested but try this update to your code

Private Sub cmdupdate_Click()
    Dim wsadd As Worksheet
    Dim Rw As Range
    Dim Search As String
    
    Set wsadd = Sheets("Historical")
    
    Search = Me.txtnam.Value
    If Len(Search) = 0 Then Exit Sub
    
    Set Rw = wsadd.Range("B:B").Find(Search, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
    
    If Not Rw Is Nothing Then
        With wsadd
            If .ProtectContents Then .Unprotect Password:="" 'add password if required
            .Range("W" & Rw.Row).Value = Me.cmbstate.Value
            .Range("X" & Rw.Row).Value = Me.txtlstfor.Value
            .Range("T" & Rw.Row).Value = Me.txtunit.Value
            .Range("V" & Rw.Row).Value = Me.cmbrefrad.Value
            .Range("U" & Rw.Row).Value = Me.txtuic.Value
            '.Protect Password:=""
        End With
        
    Else
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
    
End Sub

Dave

 

[chipsworld]

Thanks Dave...That was the ticket!

 

[dmt32]

Thanks Dave...That was the ticket!

Pleased update resolved for you.

As Joe4 mentions, when using Range.Find method in your code you need to manage the error when search value cannot be found.

Dave

 

[chipsworld]

dmt32...hate to ask but...How do I mark this as resolved?

 

[dmt32]

dmt32...hate to ask but...How do I mark this as resolved?

I asked same question once & got a really informative answer from MrExcel and broadly, anyone reading the posts will see that you have a solution but may find it helpful at later date or maybe offer a belated alternative approach which is why posts are left open.

Dave