Relative Cell Problem - Can you solve?

apwdweb

Board Regular
Joined
Aug 23, 2002
Messages
86
Hi,

I'm needing to calculate a value using surrounding cells. The VBA formula will need to use relative reference.

activecell.value = dataPage.Cells(xxx, "h").Value

where the xxx's are should be some kind of reference which will seek the the row 1 below the currentactive cell.

Again I can't use absolute references such as

activecell.value = dataPage.Cells(24, "h").Value <-- if i had been activecell = 23

This can't do as I will be autofilling this over several other cells so i need a relative reference. I am new to VBA but i think it might have something to do with R1C2 or something to that syntax.

Thanks in advance.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
apwdweb said:
activecell.value = dataPage.Cells(xxx, "h").Value

where the xxx's are should be some kind of reference which will seek the the row 1 below the currentactive cell.

Thanks in advance.

Howdy apwdweb,

I'm a little confused here. What is the "h" supposed to be?

Are you looking for the value that is in the same column one row down from your current active cell? If so then

ActiveCell.Value = ActiveCell.Offset(1,0)
 
Upvote 0
apwdweb

I think you should try "offset"

For example:
MyVariable = ActiveCell.Offset(1, 1)

This will extract the value that is 1 row below and 1 column to the right of the active cell.

You can of course put in variables in for the row and column

Hope that helps!
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top