Using variables in worksheet.range

[Caz]

I am trying to create a dynamic workbook using VBA code for
calculations. I have a sheet called "sheet_increase" which has named
ranges using the column top cell value e.g.

A B C E F
1 year me00 me30 rl30 rl50
2 2003 0.03 0.03 0.01 0.02
3 2004 0.04 0.04 0.02 0.03

I have another sheet called "sheet_2004" which has client details
along with similar titled columns e.g

A B C E F
1 name age sex me00 me30
2 jim 33 m £1300 £500
3 john 54 m £27500 £1200

What I want my macro to do is grab the name of the current sheet and
strip it down to the last 4 characters e.g. "sheet_2004" would result
in 2004 - this part I can achieve. The next value I want to grab is
the column title e.g. "me00" and bring it into the macro as variable
pRef - which I can achieve.

The remaining part of this calculation would be to multiply the
currency value by the figure gleened from the "sheet_increase" sheet
to attain a new currency value.

What I would like to do now would be to use:
where the row value = 2004 (I have code for this part)
myValue = Worksheets("sheet_increase").Range(" pRef ").Cells(c.Row -
toprow)

Obviously if the name is explicity coded into the function e.g.
Worksheets("sheet_increase").Range("me30").Cells(c.Row - toprow), it
works but I would like this to be as dynamic as possible as there are
8 different values using identical code, with only the column header
variable being different. What am I doing wrong as regards using the
pRef variable? I have tried using .Range(" & pRef & ") and a couple of
other similar pieces of code but when the macro runs it simply exits
the function on this line without any error message to help me
understand why.

TIA,

Caz

 

[RADO]

Change your code like this:

myValue = Worksheets("sheet_increase").Range(pRef).Offset(c.Row -
toprow).value

(basically, omit "" around pRef, because without quotes, it's a variable,
and with quotes, it's just a fixed string)

Then, you should define a bunch of names in your spreadsheet. I.e., before
you use the code above,
you should have a name "me00" referring to the cell with the title "me00",
and similar names for all titles (I assume you know how to define name in a
spreadsheet). That should work.

However, there are better ways to accomplish this task. If I were you, I
would pass to the macro not the title itself, but rather the address of the
cell it's located in. I.e, your pRef variable should be defined as Range,
rather than as String. In that case, your code will become:

myValue = Worksheets("sheet_increase").pRef.Offset(c.Row - toprow).value

and you don't have to define any names in your spreadsheet.

I can't help you grab the cell address because I need to know exactly how
you grabbing the title, but in general, what I would do:

Let's say, you know the row where the titles are located (your variable
"toprow", right? - by the way, I think "TitleRow" is a better name for it)
Then, I assume you also know the column where the title is located (let's
call it "TitleColumn"). So, you should set your variable pRef this way:

Dim pRef as Range

Set pRef=Worksheets("sheet_increase").Cells(TitleRow, TitleColumn)

it now should refer to the cell with the title.

Then find your value:
myValue = pRef.Offset(c.Row - TitleRow).value

Best,

RADO