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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Egress1

Active Member
Joined
Mar 2, 2003
Messages
420
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)
 

bydesign

Board Regular
Joined
Nov 29, 2003
Messages
184
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
Joined
Aug 23, 2002
Messages
86
Worked like a charm. Thank you very much for your help and expertise
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,686
Members
425,230
Latest member
DzOus

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