Trying to Determine if a Cell is equal to a Value using Range, get runtime error 1004

Question

With wbImportFile.Sheets(1)
    .Range(Cells(iFirstRow + 464, iFirstColumn)).Select
    If Not Selection.Value = "" Then
        MsgBox ("This cam has an offset")
    End If
End With

All, I am trying to determine if a specific cell in a file I am importing has a value in it. wbImportFile is a Workbook variable, and iFirstRow and iFirstColumn are Long variable (calculated in previous code).

I'm just trying to determine if the cell that is located at the iFirstRow + 464th row and iFirstColumn is empty, and when I do, I get an error on the second line that is runtime error 1004 "Application defined or object defined error."

I have also tried the code without the with statement:

If Not Range(Cells(iFirstRow + 464, iFirstColumn)).Value = "" Then
    MsgBox ("This cam has an offset")
End If

but I get the same error.

Can anyone help me?

Dmitry Pavliv · Accepted Answer · 2014-02-04 18:08:52Z

2

Try this code:

With wbImportFile.Sheets(1)
    If Not .Cells(iFirstRow + 464, iFirstColumn).Value = "" Then
        MsgBox "This cam has an offset"
    End If
End With

You can use Range object like this Range("A1:A10") or Range(Cells(1,1),Cells(10,1)), but you can't use Range object in that way: .Range(Cells(..)). You should simply use .Cells() instead.

edited Feb 4, 2014 at 18:08

answered Feb 4, 2014 at 17:34

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ARich Over a year ago

+1 But I would add that you can use .Cells with .Range, just not like the OP did. Example: .Range(.Cells(1,1),.Cells(10,2))

Dmitry Pavliv Over a year ago

@ARich, thanks:) you're right, it's exactly the thing I'm trying to tell OP:)

ilarson007 Over a year ago

@ARich Wow, what confusing rules.

ARich Over a year ago

It can be sometimes. :) Technically, your code probably would have worked if you had added .Address to the end of .Cells. Example: .Range(.Cells(iFirstRow + 464, iFirstColumn).Address).

ilarson007 Over a year ago

Okay guys, I am still having trouble understanding this whole Range, Cells thing. I have "Answered my own question" with more code below.

Collectives™ on Stack Overflow

Trying to Determine if a Cell is equal to a Value using Range, get runtime error 1004

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related