Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variables in worksheet.range
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 "Caz" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a range with three variables | Excel Discussion (Misc queries) | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions | |||
Using variables to select a range | Excel Programming | |||
Range Variables | Excel Programming |