VB: ERROR Object variable not set, ANSWER: SCROLL ROW find a name in a column hi

Question

VB: ERROR Object variable not set, ANSWER: SCROLL ROW find a name in a column

hi, i am needing help with an error i am getting to figure out an error for: Object or With Block variable not set

there are a couple of rem'd out example lines, that show a working - other purpose version.

i am novice at vb and am still trying to figure that subject out. i have been searching online for it.. thanks

the code i am working on is:

Private Sub CommandButton4_Click()
    Dim G3 As String    'work cell G3 shows: BJ:BJ (not in use..)
    G3 = RANGE("G3")    '
    Dim G6 As String    'shows: $BJ$16
    G6 = RANGE("G6")
    Dim C8 As String    'shows: BJ334:BJ1793
    C8 = RANGE("C8")
    'Application.ScreenUpdating = False 'UPDATE OFF screen jump
  
  
If 0 = 0 Then
  
  
    Dim namX As RANGE
    namX = RANGE("C8") 'ERROR: "namx" Object variable or With Block variable not set
  
    'THIS WORKS FOR SKIP scrnupdate & no hdr jump.. (from a current position & srch down)
    'Dim r1 As RANGE, r2 As RANGE
    'Set r1 = Cells.find(what:="dn", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    'ActiveWindow.ScrollRow = r1.Row - 1
  
    Set namX = Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    ActiveWindow.ScrollRow = namX.Row - 1

Else

  
'==========
'< WORKS W/ SCRN JUMP (using update off)
    Application.ScreenUpdating = False 'UPDATE OFF screen jump
      RANGE(G6).Select
      Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate    'orig
      ActiveWindow.ScrollRow = ActiveCell.Row - 1   '<< SCROLL WORKS!!
  
End If
  
    Application.EnableEvents = True     'EVENTS
end sub

==========    SHORT ANSWER:    major changes made

Private Sub CommandButton8_Click() 'SCROLL ROW to a name in a column
    Dim C8 As String    'shows: BJ334:BJ1793 (work cells)
    C8 = RANGE("C8")
    Dim G6 As String    'shows: $BJ$16 (work cells)
    G6 = RANGE("G6")

    Dim namX As RANGE
    Set namX = RANGE(C8).find(what:=RANGE(G6).Value, LookAt:=xlWhole)
    'Application.ScreenUpdating = False        'UPDATE OFF screen jump no longer needed
  
    If namX Is Nothing Then
      MsgBox "None Found"
      Application.EnableEvents = True         'EVENTS
      SendKeys ("{ESC}")
      Exit Sub
    End If
  
    ActiveWindow.ScrollRow = namX.Row - 1
    namX.Activate   '<< missing item, macro refined to get rid of a match formula that identified the destination.

    Application.EnableEvents = True            'EVENTS
    SendKeys ("{ESC}")
  
'work cell examples for DYNAMIC REFERENCES:
'C8: =SUBSTITUTE(SUBSTITUTE(CELL("address",$BJ$334),"$",""),"","")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$BJ$1796),"$",""),"","")
'G6: =SUBSTITUTE(SUBSTITUTE(CELL("address",$BJ$16),"$",""),"","")
  
End Sub

==========    WORK DONE:

PROBLEM - FIXES:    (had 3 total)

Dim namX As Range    'correct

'Dim namX As String    'wrong

Set namX = RANGE("C8")    'correct

'namX = RANGE("C8")    'wrong

After:=ActiveSheet.RANGE(C4),    'correction

'in FIND: After:=ActiveCell,    was wrong changed to above reference start point.

the problem i was having was trying to make a 1 command - find operation, without update = false, so no extra header flash.. i am still checking on that to see if is as smooth as can get it. after some testing, the flash seems to now be the same as before. did see some improvement at first - checking.

    UPDATE: flash completely gone if not changing locaction from eg: lines in hundreds to line location in thousands. else: sometimes small header flash & sometimes large..

the working example i have is: (note: i use work cell references, that are "DIMMED" so cell / column locations are dynamic, for moving cells..

----------    LAST UPDATE, appears to be working:

the short answer will be above this line. hope this is not too much, but much blood spent on getting the different versions below.

Private Sub CommandButton4_Click() 'BBB button4 BUTTON4, FIND SYM: ref in a col
    Dim G3 As String    'shows: BJ:BJ   (not in use..)
    G3 = RANGE("G3")
    Dim G6 As String    'shows: $BJ$16 (work cells)
    G6 = RANGE("G6")
  
    Dim C4 As String    'shows: BJ334    < ADDED
    C4 = RANGE("C4")
    Dim C8 As String    'shows: BJ334:BJ1793
    C8 = RANGE("C8")
  
    Dim J8 As String    'J8 shows: BN16:BN16
    J8 = RANGE("J8")
  

If 0 = 0 Then   'method3 NEW ANSWER, ABLE TO GET RID OF WORKCELL J8 (for match row destination).
  
    Dim namX As RANGE
    Set namX = RANGE(C8).find(what:=RANGE(G6).Value, LookAt:=xlWhole)
    'Application.ScreenUpdating = False        'UPDATE OFF screen jump
  
    If namX Is Nothing Then
      MsgBox "None Found 3"
      Application.EnableEvents = True         'EVENTS
      SendKeys ("{ESC}")
      Exit Sub
    End If
  
    ActiveWindow.ScrollRow = namX.Row - 1
    namX.Activate   '<< missing item, macro refined to get rid of a match formula that identified the destination.

    Application.EnableEvents = True            'EVENTS
    SendKeys ("{ESC}")
  
    'RANGE(J8).Calculate   'get rid of J8
    'Application.Goto RANGE(RANGE(J8).OFFSET(0, 0)) 'WORKS from single work cell    << GOTO, FIND ANY ROW DESIG    << << <<
    'ActiveWindow.ScrollRow = ActiveCell.Row - 1     '<< SCROLL WORKS!!
  
  
ElseIf 0 = 0 Then   'method2 YES: does find in a range wo update off (change to 0 = 1 to select others below)
  
    Set namY = RANGE("G6") '.value '?
    Dim namX As RANGE       '< FIXED string rng FIND
    Set namX = RANGE("C8")    '< FIXED set
  
    'THIS WORKS FOR SKIP scrnupdate & no hdr jump.. (from a current position & srch down)
    'Dim r1 As RANGE, r2 As RANGE
    'Set r1 = Cells.find(what:="dn", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    'ActiveWindow.ScrollRow = r1.Row - 1
  
    Set namX = Cells.find(what:=ActiveSheet.RANGE(G6).Value, After:=ActiveSheet.RANGE(C4), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    ActiveWindow.ScrollRow = namX.Row - 1
    SendKeys ("{ESC}")
  
    'make an item not found msg
    If namX Is Nothing Then MsgBox "None Found 2"   'not working
    'If r1 Is Nothing And r2 Is Nothing Then MsgBox "nothing found"
  
  
Else                'method1
'==========
'< WORKS W/ SCRN JUMP (using update off)
    Application.ScreenUpdating = False        'UPDATE OFF screen jump
      RANGE(G6).Select
      Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate    'orig
      ActiveWindow.ScrollRow = ActiveCell.Row - 1   '<< SCROLL WORKS!!
  
End If
  
'    Application.EnableEvents = True           'EVENTS
'end sub

Thanks for help !!

Explanation / Answer

Hi..

Hi,

The line will error out because you have dimmed namX as a range so when you come to assign a value to it you have to use the SET command.

Having said that I was a little confused by your code because you open the IF statement with the line

If 0 = 0 Then

0 will always be 0 so the code after the ELSE statement can never execute...

Dim namX As RANGE.
SET   namX = RANGE("C8") 'ERROR: "namx" Object variable or With Block variable not se

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.