CODEWITHSUNDEEP
excelvbafind
Rocobop
Rocobop

Reputation: 1

VBA: Getting error '1004' while trying to define Range object (WorkSheets/ActiveSheet used)

Sub GoToCont()

    Ind = ActiveCell.Value                        'value I am searching for
    RPointer = ActiveCell.Row                     'Pointer for active cell row
    Dim Mark As Range                             'Tried defining Mark because I kept getting errors when trying reference the cell directly in the Find Method
    Set Mark = ActiveSheet.Range(RPointer, 1)     'Debug shows Mark=None and this is where I get '1004'

    Columns(1).Find(What:=Ind, After:=Mark, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=2).Activate

End Sub

I am basically trying to find a value in the first column that matches the value of the active cell, starting from the current row, going up the column. Suggestions on other ways to do it are welcome - But, I'd like to also know what I'm doing wrong.

Thanks for the help ;)

P.S - I will wrap the ActiveCell commands, I just kept it as simple as possible until I get it to work.

Upvotes: 0

Views: 349

Answers (3)

Variatus
Variatus

Reputation: 14373

I disagree with the two answers above in as far as your intention is correct. You are trying to define a cell and use the correct syntax for doing so. But it seems you got mixed up between Cell and Range. My colleagues above correctly point out that you should use the syntax to define a Range if you wish to define a range. I differ in suggesting that you shouldn't define a cell as a range. A cell is a cell and you are right in trying to use cell syntax.

Dim Mark as Range
Dim Cell as Range
Set Mark = Range("A1")
Set Cell = Cells(1, "A")

The variation Set Mark = Range("A" & RPointer") works perfectly but is lousy VBA because it adapts Excel worksheet syntax into VBA. Once you try to write advanced code where both row and column are calculated variables this method is far to convoluted to be practicable. It shouldn't be recommended to you here. You should use Set Mark = ActiveSheet.Cells(RPointer, 1). Set Mark = ActiveSheet.Cells(RPointer, "A") may be easier to read and that's why I use it at times if the column will not be calculated. Since you have already advanced to referencing cells with (RPointer, 1) stick with that method.

BTW, using the Find function to Activate a cell is asking for trouble (the What isn't found*). It also contravenes the rule never to Select or Activate anything while using VBA except under very few and very particular circumstances (which don't apply here). The Find function returns a range object. Therefore ...

Dim Fnd as Range
Set Fnd = Columns(1).Find(What:=Ind, After:=Mark, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=2)
If not Fnd Is Nothing Then

Upvotes: -2

PaulS
PaulS

Reputation: 940

They way a Range Object is formed is Range (cell1, cell2).

You are trying to declare it with Range (Row, Column).

To make it work you should change it to Range("A" & RPointer), to get the first Row in Column A.

See also

Upvotes: 1

Beek
Beek

Reputation: 406

Your problem is in the way the line Set Mark = ActiveSheet.Range(RPointer, 1) is written.

It should either be Set Mark = ActiveSheet.Cells(RPointer, 1) or Set Mark = ActiveSheet.Range("A" & RPointer)

Upvotes: 4

Related Questions