Referring to Excel cell value in VBA

[BH]

Hi I was hoping that someone can point me in the right direction in showing me how to refer to a cell value inside VBA code.

I am working with pivot tables where i select from a list to give me the values i require. So far i have managed to create and run a macro that selects the values for me. My problem is that I want to make the code generic and use a cell value as part of the code.

At the moment i have:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[Practice]").VisibleItemsList = Array( _
"[PRACTICE].[PRACTICE].[Practice].&[TRENTSIDE]")


What I would like is to remove 'TRENTSIDE' and instead refer to a cell value i.e. WorkSheet("PackCreator") Sheets("Pack") Range("I8")

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[Practice]").VisibleItemsList = Array( _
"[PRACTICE].[PRACTICE].[Practice].&[CELL VALUE!!!!!!!]")


I hope I have explained this well enough! I am sure there is a simple way but I am struggling....thanks for any help

 

[SkipVought]

Hi,

Msgbox sheets("pack").range(I8)

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

 

[BH]

Thanks for your assistance Skip

I have replaced TRENTSIDE with the cell reference and get a compile error: Expected: list seperator or)

It does not appear to like the sheet name "Pack"

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[Practice]").VisibleItemsList = Array( _
"[PRACTICE].[PRACTICE].[Practice].&[Msgbox sheets("Pack").range(I8)]")

Where am I going wrong???

 

[SkipVought]

what I posted is an EXAMPLE of how to reference a range on a sheet.

It was not intended to plug into your code, as is!

Rather than telling us HOW you think it ought to be done, please explain WHAT it is that you want to accomplish in prose.

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

 

[BH]

Hi

WHAT i am trying to do (but failing miserable) is to use a value from a cell to replace fixed text in my code. In my 1st posting example I have fixed text of "TRENTSIDE", i would like to refer to a cell value to replace the text "TRENTSIDE"

So if the Cell (I8) value = SOUTHSIDE then my code would change from:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[Practice]").VisibleItemsList = Array( _
"[PRACTICE].[PRACTICE].[Practice].&[TRENTSIDE]")

TO:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[Practice]").VisibleItemsList = Array( _
"[PRACTICE].[PRACTICE].[Practice].&[SOUTHSIDE]")


Sorry but I am struggling with writing my explanation any clearer...

If it helps, TRENTSIDE is one of 21 names I can select when I physically select from the list on my pivot dropdown. At the moment I have managed to automate the process of selection by using code but to make it work I have reproduced the code 21 times changing just the name text(i.e. TRENTSIDE to SOUTHSIDE). All i want is to use one code and use a cell value to change the name.

Is this any clearer?

 

[SkipVought]

You are working with an OLAP PivotTable.

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[PRACTICE].[PRACTICE].[Practice]").VisibleItemsList = Array( _
        "[PRACTICE].[PRACTICE].[Practice].&[[highlight]" & sheets("Pack").range(I8) & "[/highlight]]")

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

 

[BH]

Hi thank you for this but now I get a Run-time error!

Run-time error '1004':

Application-defined or object-defined error

The VBA code indicates that the cell is empty? Am i approaching this in the right way.....using a cell value to drive the pivot dropdown criteria??

 

[PHV]

[PRACTICE].[PRACTICE].[Practice].&[" & Sheets("Pack").Range([!]"[/!]I8[!]"[/!]) & "]")

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

 

[BH]

Fantastic it now works! Thank you very much PHV and Skip

 

[N1GHTEYES]

PHV - are you suggesting the best way to learn is Practice, Practice, Practice <grin>?

Tony

 

[SkipVought]

@PHV

What? You didn't like my undeclared, unknown, implied integer variable, that would fail to compile????
[sub]Oh, I guess I don't like it much, either. [/sub]

Merry Christmas! ;-)

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