Update row if record found

[sharky12345]

I'm trying to update the row on a sheet if a record is found, I'm trying this but it's not working;

Set ws = Worksheets("Training")
Set SearchRange = Range("A2", Range("A65536").End(xlUp))
Set FindRow = SearchRange.Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
Set Row = FindRow.Row
realDate = Me.Controls.Item("TextDate").Text
ws.Cells(Row, 4).Value = DateSerial(Year(realDate), Month(realDate), Day(realDate))
ws.Cells(Row, 5).Value = Me.Controls.Item("ComboType").Text
ws.Cells(Row, 6).Value = Me.Controls.Item("ComboInst1").Text
ws.Cells(Row, 7).Value = Me.Controls.Item("ComboInst2").Text

I'm getting Type Mismatch with this highlighted (within FindRow.Row) ;

.Row

 

[dmt32]

Hi,
change this:

Set Row = FindRow.Row

to this:

Row = FindRow.Row

Where your Row variable would be declared as a Long Data type - although personally, I would use something like r instead of Row.

Dave

 

[sharky12345]

Hi Dave,

Thanks - but now I'm getting a run time error 91, 'Object variable or with block variable not set' with the following highlighted;

Row = FindRow.Row

 

[dmt32]

Change your variable Row to something else like r

           r = FindRow.Row
            realDate = Me.Controls.Item("TextDate").Text
            ws.Cells(r, 4).Value = DateSerial(Year(realDate), Month(realDate), Day(realDate))
            ws.Cells(r, 5).Value = Me.Controls.Item("ComboType").Text
            ws.Cells(r, 6).Value = Me.Controls.Item("ComboInst1").Text
            ws.Cells(r, 7).Value = Me.Controls.Item("ComboInst2").Text

r should be declared as Long Data type

see if this solves your problem.

Dave

 

[sharky12345]

That's works great Dave, thanks - I think part of my problem may have been I had Row as Range but regardless you've solved the issue!

 

[dmt32]

That's works great Dave, thanks - I think part of my problem may have been I had Row as Range but regardless you've solved the issue!

I did state in my first post that Row variable would be declared as a Long Data type
but Row is one of those words in VBA best avoided for use as a variable.

Also, you may want to consider adding a test to your code to cover search values not found e.g.

        If Not FindRow Is Nothing Then
            r = FindRow.Row
            'rest code
        Else
            MsgBox Me.ComboBox1.Value & Chr(10) & "Record Not Found", 48, "Not Found"
        End If

Glad resolved for you.

Dave