Run-time error ‘1004’

[rob c]

I copied some bode from a book that takes up about 3 pages that isn't working.

I get the following error message:

"Run-time error ‘1004’;
Method ‘Range’ of object ‘_Worksheet’ failed"



When I click on debug the following line is highlighted:

"Set rgTopLeft = ws.Range(FOUND_LIST).Offset(1, 0)"

Any suggestions on how I can find out what needs to be changed in order to
make this work?

Regards,

Rob

 

[Brotha Lee]

Rob C,

It is probably in the variable FOUND_LIST. When you click on debug the
following and the line is highlighter hover over the FOUND_LIST with your
mouse. Probably it is returning EMPTY.

If this is the case you have to set the variable somewhere. For example:
FOUND_LIST = "A1", however this is depending on what you code should do and
which range have to be set in your code.


brotha lee

 

[rob c]

Brotha,

Thanks for the assistance.

When I did the hover, it returned FOUND_LIST" =FoundList", so I gave a range
on the spreadsheet the name "FoundList". This removed the data from all the
fields in the range (I am trying to get certain data from the range to be
placed in a different column, but keep it in the cells it was already as
well) except for the first field and gave me a different error message,
"Compile error Sub or Function not defined". and highlighted "wscells" in
the function below:

' sets a range reference to the range containing
' the list = the product column
Private Function GetSearchRange(ws As Worksheet) As Range
Dim lLastRow As Long

lLastRow = ws.Cells(5000, 1).End(xlUp).Row
Set GetSearchRange = ws.Range(wsCells(1, 2), _
wsCells(lLastRow, 2))
End Function

Regards,

Rob

 

[Jim Thomlinson]

ws is the worksheet. To get at the cells property you need the dot so
ws.cells or better still.

' sets a range reference to the range containing
' the list = the product column
Private Function GetSearchRange(ws As Worksheet) As Range
Dim lLastRow As Long

with ws
lLastRow = .Cells(5000, 1).End(xlUp).Row
Set GetSearchRange = .Range(.Cells(1, 2), _
.Cells(lLastRow, 2))
end with
End Function

 

[rob c]

Jim,

Thanks for the advice, I'm going to try to keep the code as close to what it
was in the book as possible so I used the "ws.cells" suggestion.

Now I get a different error message, "Run-time error '1004';
Application-defined or object-defined error" The following is highlighed when
I click on debug (Else and End If are not highlighted but it shows the
context at litlte).


"
Else
Set rgDestination = rgDestination.End(xlDown).Offset(1, 0)

End If"