Excel VBA - naming a relative reference 1

[smooveb]

Hi,

I'm pretty new to using relative references in my VBA code. I use excel 2003, and write fairly simple programs most of the time.

I'm wanting to start in a particular cell, which will change every time I run the program. I will then move four spaces to the left to set one of my variables for analysis.

ActiveCell.Offset(0, -4).Select
Lot = sheet.activecell.value

I'm then going to run through this list until I'm at the end of the list.

If lot <> "" then
Do X

If I was using an absolute reference, I'd be able to use the following syntax to proceed through the list, like I am used to:

i = i + 1
lot = sheet.cells(i, 3).value

But I'm not sure of the syntax for doing this type of thing with relative references. At the point where I'm setting Lot above for the first time (Lot = sheet.activecell.value)is there a way to determine the values of row and column I am in (i.e. the "i" and the "3").

Please let me know what you all think - sorry if I'm not being clear enough, but if you have questions I'll try to clear up my question more.

Thanks!
Barrett

 

[SkipVought]

Hi,

What you've given us is your opinion about how the problem ought to be solved.

Why don't your tell us what the actual functiona requirement is, withour attempting to describe a solution. I'd wager that your requirement dosen't even need VBA code to work.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[electricpete]

There are all kinds of ways to ways to manage cell addressing.

I don't understand exactly what your example is doing. But here is one way to determine row and column of a cell
range("B3").Row returns 3

range("B3").Column returns 2