Update row if record found

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,354
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to update the row on a sheet if a record is found, I'm trying this but it's not working;

Code:
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) ;

Code:
.Row
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,337
Office Version
  1. 2019
Platform
  1. Windows
Hi,
change this:

Code:
Set Row = FindRow.Row

to this:

Code:
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

Well-known Member
Joined
Aug 5, 2010
Messages
3,354
Office Version
  1. 2016
Platform
  1. Windows
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;

Code:
Row = FindRow.Row
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,337
Office Version
  1. 2019
Platform
  1. Windows
Change your variable Row to something else like r

Code:
           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

Well-known Member
Joined
Aug 5, 2010
Messages
3,354
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
7,337
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
        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
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,286
Messages
5,836,425
Members
430,428
Latest member
Deepak Tanwar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top