Passing Relative Named Variables in VBA

[RacerChris]

*** Newbie Warning ***

I have an application that uses Named Variables with relative references. In other words, I use a rows to do computations that reference other columns in the row for values. The value in these cells changes depending on which row it is being calculated in.

I want to pass the value of the named variable according to which row the VBA Module is set to; then do a procedure, then loop through the rows.

Can someone help me figure out how to do this?

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Please post your code.

In general this references rows 1 to 5, columns 2 to 4...

Dim lRow as long, iCol as integer
for lRow = 1 to 5
  for iCol = 2 to 4
    msgbox cells(lrow, icol).value
  next
next

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RacerChris]

AreaWR = Range("Ao39") 'WORKS
AreaWR = Range("SD") 'Works
AreaWR = Range("AWR") 'Works only on Line 1

Thanks for the reply. The first and second works fine. The difference is that the first one use relative reference while the second absolute.

The third one seems to select the right column, but extracts the value from Row 1, not the Row I want it to extract it from.

Like I said, I'm a newbie and perhaps I am missing something obvious...

 

[SkipVought]

Range("SD") is INVALID, unless you have a RANGE Named "SD".

What version of Excel, please?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

hmmmm....

Open Insert > Name > Define and find the RefersTo range reference for each of your Named Ranges.

Please post each.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RacerChris]

Skip,

I am using Excel 2007. And Yes SD is a named range. Again, works fine if absolute, but defaults to Row 1 if relative.

Not sure how to paste in the Named Ranges, but

SD refers to Calcs!$r$3
AWR refers to Calcs!$AO11

Thanks again!

 

[SkipVought]

[tt]
SD refers to Calcs!$r$3
AWR refers to Calcs!$AO11
[/tt]
BOTH of these references are ABSOLUTE with respect to COLUMN. The FIRST is ABSOLUTE with respect to row, while the SECOND is not.

Please explain how you are using or intend to use your named ranges and references.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RacerChris]

This is what the spreadsheet currently does:

The current spreadsheet is for structural calculations. Both input items and calcuated cells are done in the same row. This allows doing the same calc with different inputs simply by copying the row over and over. The calcs refer to both inputs outside the row which are ABSOLUTE (SD) and inside the row which are RELATIVE (AWR).

In order to do what I want, I had circular references which seemed to work fine, but I noticed that it does not result in the correct answer. So I want to write a VBA routine that changes the inputs, extract the results and then sets the correct answer for each ROW.

Hope this makes sense.

 

[SkipVought]

Please be more specific regarding how you are using these named ranges in your VBA Code.

Give a concrete example (specific values in specific row, column) and the current result and the expected result.

Also, what's in SD and in column AO?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

AWR refers to Calcs!$AO11

Be careful with relative references in names. They can be quite useful when are in formulas; they are relative to calling cells. With the same name you can refer to different data.
When you define/view it, the address is relative to active cell. The same is in vba, to take the full advantage you need to change selection while looping. So the best way of addressing cells was shown in Skip's first answer (you can add worksheet's reference if the data is not in active worksheet).

combo

 

[RacerChris]

Thanks guys for the reply and assistance. Hopefully this will help you folks understand the problem better.

COMBO: "When you define/view it, the address is relative to active cell. The same is in vba, to take the full advantage you need to change selection while looping."

I tried this, but couldn't get it to work. See Below:

AWR is the value I am trying to extract. It is located at cell Ao39 and is also define as AWR with a relative reference to $Ao39.

This works:
AreaWR = Range("Ao39")

This doesn't work:
Range("A39").Select
AreaWR = Range("AWR")

It extracts the value in Row 1, not Row 39. Perhaps there is a better way to select the Row which will make the value extract correctly?

 

[SkipVought]

AVOID the Select and Activate methods as much as possible.

  AreaWR = cells(39, 1).Value

using either literals or variables.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RacerChris]

Guys,

One other thing. Although Skip's method, ie, using Cells works, I'd rather not use it, since it could result in an error if a Column is inserted in the spreadsheet. I'd rather use relative references, so the VBA code works even if the spreadsheet is modified...

 

[SkipVought]

How would it return an error?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

AWR refers to Calcs!$AO11

Generally, it is not true. See name definition with any cell selected any cell in row 11 and row 12 for instance.
After Range("A39").Select, assuming that you were in the row other than 39, AWR called in VBA refers to other address (and value).
There is another little problem too. Names with relative references change their 'RefersTo' property depending on selection, but 'Address' behaves differently.
Assuming speed and simplicity, I am totally with Skip: AVOID the Select and Activate methods as much as possible.


combo

 

[RacerChris]

SKIP - "How would it return an error? "

If I use the Cells command with a specific Column Number, then if a column is inserted into the spreadsheet, then won't the Cells command not be updated and therefore reference the wrong cell?

SKIP - "AVOID the Select and Activate methods as much as possible.
"

As Combo suggested, I included the Select command only to set the "focus" on the correct row in hopes that the relative reference, ie, AWR would use that row - it doesn't :-(

 

[SkipVought]

There are many other ways, AVOIDING the select method.

 AreaWR = cells(lRowNbr, cells.find("YourColumnHeading").column).Value

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RacerChris]

Skip,

Thanks for the tip. That may be a good alternative. I guess I should abandon using Named references that are relative. It seems they don't work in VBA. Do you agree?

 

[SkipVought]

YES!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}