Specify Range error

[SIGE]

Hello,

I run into a Run-time error '1004'
Method "Range" of Object "_Worksheet" failed.

when trying trying to specify this range:

iS=Application.WorksheetFunction.Match(Sheet1.Range("A3"), Sheet1.Range(Cells(6, 1), Cells(Rows.Count, 1).End(xlUp)), 0)

Can someone pinpoint what I am doing wrong in my Range-specification?

Thanks in advance, Sige

 

[VoG]

I wouldn't use Is as a variable name. Try

X = Application.WorksheetFunction.Match(Sheet1.Range("A3"), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(Rows.Count, 1).End(xlUp)), 0)

 

[SIGE]

Hi Peter,

 iTF = Application.WorksheetFunction.Match(UserAdministration.Range("A3"), UserAdministration.Range(Cells(6, 1), Cells(Rows.Count, 1).End(xlUp)), 0)
MsgBox iTF

Still generates the same error

The sheet UserAdministration(Usersheet) exists as well. I am confused...

Any other suggestion on what might be wrong?
Sige

 

[Andrew Poulsom]

You're still not qualifying the Cells property. Try:

    With UserAdministration
        iTF = Application.WorksheetFunction.Match(.Range("A3"), .Range(.Cells(6, 1), .Cells(Rows.Count, 1).End(xlUp)), 0)
    End With

 

[SIGE]

Hi Andrew,

It are such subtleties that are killing ...
Still do not see/understand completelythe difference between your solution and my initial code...
Anyway,thanks a lot for your help!

Up to the next problem!
Sige

 

[Andrew Poulsom]

Your code should have been:

iTF = Application.WorksheetFunction.Match(UserAdministration.Range("A3"), UserAdministration.Range(UserAdministration.Cells(6, 1), UserAdministration.Cells(Rows.Count, 1).End(xlUp)), 0)