Value of cell next to a cell defined as an object

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
I've set an object by using .find to identify a cell.

Set ACCTS_PAYABLE_OBJECT = Sheets(BAL_SHT_TAB_NAME).Range(TAG_RANGE).Find(SEARCH_STRING, LookIn:=xlValues, LookAt:=xlPart)

Is there a cleaner way to get the value of the cell in the next column to the right from the object cell?

Right now I'm using:
ACCTS_PAYABLE_VALUE = Worksheets(BAL_SHT_TAB_NAME).Cells(ACCTS_PAYABLE_OBJECT.Row, 2).Value

but I thought I had read something that gets the value more easily/directly but I can't find it now.

If anyone has an idea on this I would appreciate the help.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
In this case, you'll probably want to use Offset. Here's an example....

Code:
Dim thisCell As Range
Set thisCell = Sheets("Sheet1").Cell(1, "A")

Dim OffsetValue As String
OffsetValue = thisCell.Offset(0, 1).Value

It's used in this fashion....

Offset(row, column)

So with a 1 in the column, it will offset the object to the right by one cell.

or you could use -1 and get the value in the previous column.

Same would go for rows higher or lower, it would just be in the row position instead.

In theory you might be able to use...

Code:
ACCTS_PAYABLE_VALUE = ACCTS_PAYABLE_OBJECT.Offset(0, 1).Value

but that would be if the object is a range. How is the object declared?
 
Last edited:

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
Good question. I didn't know what declaration to use so left it as a variant.
ACCTS_PAYABLE_VALUE = ACCTS_PAYABLE_OBJECT.Offset(0, 1).Value does work and I think that's what I had read. I've now declared it as a Range and everything still works.
Thanks RJ
 

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
I thought so. I just didn't want to say with absolute clarity without testing it. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top