Run-time error 1004 when using Offset function

Question

How do I retrieve the cell next to the one that has been defined? Cell "A2" stores AT and I want to display Austria which i have displayed in cell "B2" instead of AT. I need this functionality in a For loop.

AT  Austria
IT  Italy
FR  France

I need to do the For loop using AT (for other purposes) but i want to write away the country name (Austria or Italy etc)

The macro below gives run-time error 424 Object required.

Sub test()
    Dim country
    Dim country_list
    Dim counter

    country_list = Worksheets("Sheet1").Range("A2:A4")
    counter = 1

    For Each country In country_list
        Worksheets("Sheet2").Cells(counter, 1).Value = country.Offset(0, 1).Value
        counter = counter + 1
    Next country
End Sub

L42 · Accepted Answer · 2014-08-25 02:02:54Z

1

You just need to use set for this to work the way you want.

Set country_list = Worksheets("Sheet1").Range("A2:A4")

It returns error sine your variable was not assigned an object.

answered Aug 25, 2014 at 2:02

L42

19.8k11 gold badges47 silver badges70 bronze badges

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

Comments

David Zemens · Accepted Answer · 2014-08-24 18:21:54Z

0

Worksheets("Sheet1").Range("A2").Offset(0,1).Value

As it is right now, you're not passing a valid range to the Range(country)... method. You have defined country to be the value of the cell "A2".

The 1004 error means that you are attempting to access a range by address which either does not exist, or cannot be accessed. In this case, the range simply doesn't exist.

answered Aug 24, 2014 at 18:21

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

4 Comments

wiser man Over a year ago

thanks David, that helps. However, i still want to make the reference to country, as in the For loop i cannot say "A2" because it can also be "A3" etc

wiser man Over a year ago

so, what do i need to do for the following to work: Country.Offset(0,1).Value

David Zemens Over a year ago

You need to show more of your code. Country must be a valid Range object at a minimum.

wiser man Over a year ago

thanks David... i changed my Question (providing more code) and i got the answer in the meantime. thanks for your help!

Collectives™ on Stack Overflow

Run-time error 1004 when using Offset function

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related