Offset vba object

Question

I have a simple Macro that has been working for months and now all of the sudden its broken when I reformatted (clue but I cant figure it out). I know the problem center around the offset function, which seems to have some problems.

I would like to use something else (Cells) but I cant figure out how.

Specifically, I have a Range that I am checking through and altering nearby cells.

Is there a way to do something like this with 'Cells'

Dim x As Variant
x = Worksheets("Book1").Range("A131")
If (x = some value And x>0) Then
x.Offset(0,7).Value = some value
End if

now excel is showing me an error that Object is required

Can you update your code to exactly the code that's causing the problem? For example having (x = some value would be error-prone since some value is 2 words... Please be more explicit in your code sample so we can more easily determine where the problem is... — John Bustos
– John Bustos, Commented Feb 27, 2013 at 17:32

Dan Metheus · Accepted Answer · 2013-02-27 16:37:18Z

2

You need to use SET:

SET x = Worksheets("Book1").Range("A131")

When you don't use SET you are returning the value that is in the Cell, not the actual range object (which is required to use Offset())... after using X use x.Value to return the contents of the cell.

answered Feb 27, 2013 at 16:37

Dan Metheus

1,43810 silver badges16 bronze badges

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

2 Comments

AbeeCrombie Over a year ago

thanks, but I am using SET in the macro for the Range Value. Sorry I didnt use in the sample code as the 'For each's get annying to read

Dan Metheus Over a year ago

I tested your code as you posted it and it returned the error you mentioned ("object is required). I changed it to include SET and it worked as expected. Can you update your question with the code you are actually using?

Dan Metheus · Accepted Answer · 2013-02-27 17:33:44Z

1

Your code using Cells():

Dim x As Variant
Dim ws as Worksheet

Set ws = Worksheets("Book1")
x = ws.Cells(131, 1).Value ' row 131, column 1 (A131)
If (x = some value And x>0) Then
   ws.Cells(138, 1).Value = someValue ' row 138, column 1 (A138)
End if

answered Feb 27, 2013 at 17:33

Dan Metheus

1,43810 silver badges16 bronze badges

Collectives™ on Stack Overflow

Offset vba object

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related