Relative Cell Problem - Can you solve?

apwdweb

Board Regular
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.

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Egress1

Active Member
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.

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)

bydesign

Board Regular
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!

apwdweb

Board Regular
Worked like a charm. Thank you very much for your help and expertise

Replies
7
Views
256
Replies
3
Views
141
Replies
3
Views
99
Replies
0
Views
304
Replies
4
Views
375

1,186,808
Messages
5,959,933
Members
438,455
Latest member
Beverly Jarrell

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.

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

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