+ Reply to Thread
Results 1 to 5 of 5

Getting "Object variable or With block variable not set" error with Find function

Hybrid View

enphynity Getting "Object variable or... 06-19-2013, 01:18 PM
AB33 Re: Getting "Object variable... 06-19-2013, 01:44 PM
enphynity Re: Getting "Object variable... 06-19-2013, 02:07 PM
Norie Re: Getting "Object variable... 06-19-2013, 02:31 PM
enphynity Re: Getting "Object variable... 06-19-2013, 05:47 PM
  1. #1
    Registered User
    Join Date
    12-21-2006
    MS-Off Ver
    Office 2013
    Posts
    73

    Getting "Object variable or With block variable not set" error with Find function

    I am trying to allow a user to double-click on a cell in column 2 on Sheet1 and it will take them to a cell on a different worksheet where the date matches the date that is on the same row in column 1 on Sheet1. Code is below:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim fRow As Integer
      Dim fSheet, fDate As Variant
      
      If (Target.Row < 3) Or (Target.Font.FontStyle = "Bold") Or (Cells(2, Target.Column).Value <> "Notes") Then Exit Sub
      fSheet = Sheet1.Cells(1, Target.Column).Value
      fDate = Sheet1.Cells(Target.Row, 1).Value
      Sheets(fSheet).Activate
      fRow = ActiveSheet.Range("A3:A20000").Find(fDate, , xlValues).Row
      Sheets(fSheet).Cells(fRow, 1).Select
      Application.Goto ActiveCell, Scroll:=True
      
    End Sub
    I am getting the error on the line with the range.find. I've tried multiple ways to write it but keep getting the error.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Getting "Object variable or With block variable not set" error with Find function

     Set fRow = ActiveSheet.Range("A3:A20000").Find(what:=fDate, LookIn:=xlValues, lookat:=xlWhole)
    if you want to use a find/match function

  3. #3
    Registered User
    Join Date
    12-21-2006
    MS-Off Ver
    Office 2013
    Posts
    73

    Re: Getting "Object variable or With block variable not set" error with Find function

    Thanks for the reply but it's still not working. I need the row where the date matches. I changed the variable type to "Object" and changed "Sheets(fSheet).Cells(fRow, 1).Select" to "Sheets(fSheet).Cells(fRow.row, 1).Select" but still getting the error, except on the aformentioned line.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Getting "Object variable or With block variable not set" error with Find function

    Try this.
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim idx As Variant
      
      If (Target.Row < 3) Or (Target.Font.FontStyle = "Bold") Or (Cells(2, Target.Column).Value <> "Notes") Then Exit Sub
    
      idx = Application.Match(Me.Cells(Target.Row, 1).Value2, Sheets("Sheet2").Range("A:A"), 0)
    
      If Not IsError(idx) Then
            Cancel = True
            Application.Goto Sheets("Sheet2").Cells(idx, 1), True
      End If
    
    End Sub
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    12-21-2006
    MS-Off Ver
    Office 2013
    Posts
    73

    Re: Getting "Object variable or With block variable not set" error with Find function

    That worked perfectly, thank you!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1