VB: Define a RANGE for a FIND operation hi, i am having trouble witht the follow

Question

VB: Define a RANGE for a FIND operation

hi, i am having trouble witht the following example. the error is: Object Required

no sure if i am doing this right, thanks.

i am trying to make a macro to go to a specific name, in a names column.

i have some other examples below that work, but i am trying to set a range for the variable: namX

Private Sub CommandButton4_Click()

    Dim G6 As String    'work cell shows: $BJ$16
    G6 = RANGE("G6")
    Dim C8 As String    'shows: BJ334:BJ1793
    C8 = RANGE("C8")
  
    Application.ScreenUpdating = False 'UPDATE OFF screen jump    TRYING TO GET RID OF THIS LINE, so 1 jump / move
   
  
    'NEED TO ID 1 COL & START FROM A TOP ROW NUMBER
    Dim namX As String

    namX = RANGE("C8")

    'THIS WORKS FOR skip scrnupdate & no hdr jump.. (srch from a current position & 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
  

    'ERROR namX: object required      << Line Working On (set namX to search a range)
    Set namX = Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

    ActiveWindow.ScrollRow = namX.Row - 1

    Application.EnableEvents = True     'EVENTS
End Sub

==========    OTHER NOTES:

Private Sub CommandButton4_Click()
    Dim G3 As String    'work cell G3 shows: BJ:BJ
    G3 = RANGE("G3")    'need?
    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
  
  
    'NEED TO ID 1 COL & START FROM A TOP ROW NUMBER
    Dim namX As String
    'Dim namesColNumber As Long    'need?
    namX = RANGE("C8")
    'namesColNumber = RANGE(namesCol).Column   'need?
    'Dim namX As RANGE
  
    'THIS WORKS FOR SKIP scrnupdate & no hdr jump.. (from a current position)
    '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
  
  
    'ERROR namX: object required
    Set namX = Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    'Set namA = Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext) 'no
    ActiveWindow.ScrollRow = namX.Row - 1
  
  
'==========
'< 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
  

' < USING:
'      RANGE(G6).Select   '< USING
    'If Selection.Column = RANGE(G3).Column Then 'SYM COL SRCH NAME < want to make a 1 line / command reference to: cells.find in place of selectio.column
    'If Cells(ActiveCell.Row) = RANGE(G6).Row Then
    ' Cells(ActiveCell.Row, G3).Select
    'Else
    ' RANGE(G6).Select
    'End If
    
' < USING
'      Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate    'orig USING
    'End If
  
    'namesCol.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
      SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate    'invalid qualifier?
    
    'RANGE(G3).find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
      SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate    'type mismatch
  
    'RANGE(G3).Column.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
      SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate    'no


'THIS WORKS if col selected is col G3:
    'If Selection.Column = RANGE(G3).Column Then 'SYM COL SRCH NAME
  
    'Cells.find(what:=ActiveSheet.RANGE(G6), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
      SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    'End If
  
    'ActiveWindow.ScrollRow = ActiveCell.Row - 1   '<< SCROLL WORKS!!
    Application.EnableEvents = True     'EVENTS
End Sub.

Thanks for help !!

Explanation / Answer

Hi..

I read the upper part of your code (did read the notes part), but I found you typed

Dim namX As String
namX = Range("C8")

Afterwards you type

Set namX = Cells.Find(........

Cell.Find returns a Range, therefore namX should be set as a Range, but you defined it as String previously.

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

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