Pivot Tables: counting columns and matching
 

Maybe something like:

Dim myPT As PivotTable
Set myPT = ActiveSheet.PivotTables(1)
MsgBox myPT.TableRange1.Columns.Count

And application.match seems ok to me.

You could use

dim foundcell as range
with activesheet
set foundcell = .range("a:a").find(what:="griev", ....)
end with
if foundcell is nothing then
'not there
else
'is there
end fi

but it seems pretty much the same to me.

Willy wrote:

Hey! I have two questions.

I'm trying to count the columns on a sheet to insert dynamic formulas and copy them accross to all my rows being used. This is the current formula I'm using to get my column count:

Dim Lcol

Set rng = ActiveSheet.UsedRange

Lcol = (rng.Columns(rng.Columns.Count).Column)

MsgBox Lcol

i'm trying to count the columns being used in the piviot table because the above formula includes the header. My piviot table will always be a min. of 3 columns, but with the header, the count will always be a min. of 6 columns. Is there any way that I can count the columns in the pivot table instead?

My second question is this, in my pivot table I have a heading named greiv, if greiv appears I want the row number in which it appears otherwise I want a different number, this is what i currently use.

matchRange = Application.Match("GRIEV", Range("'Reason Report'!A:A"), False)

but if griev does not appear, i get an error message, is this another way to do this without using error handling?

Thanks in advance for any help you can give!

Willy

--

Dave Peterson

Pivot Tables: counting columns and matching
 

Dave, I am having retrieving data from an external source, MS Fox Pro. I can
not get a driver to appear in the data type selection box?

How can I fix this?

Bob

"Dave Peterson" wrote:

Maybe something like:

Dim myPT As PivotTable
Set myPT = ActiveSheet.PivotTables(1)
MsgBox myPT.TableRange1.Columns.Count

And application.match seems ok to me.

You could use

dim foundcell as range
with activesheet
set foundcell = .range("a:a").find(what:="griev", ....)
end with
if foundcell is nothing then
'not there
else
'is there
end fi

but it seems pretty much the same to me.

Willy wrote:

Hey! I have two questions.

I'm trying to count the columns on a sheet to insert dynamic formulas and copy them accross to all my rows being used. This is the current formula I'm using to get my column count:

Dim Lcol

Set rng = ActiveSheet.UsedRange

Lcol = (rng.Columns(rng.Columns.Count).Column)

MsgBox Lcol

i'm trying to count the columns being used in the piviot table because the above formula includes the header. My piviot table will always be a min. of 3 columns, but with the header, the count will always be a min. of 6 columns. Is there any way that I can count the columns in the pivot table instead?

My second question is this, in my pivot table I have a heading named greiv, if greiv appears I want the row number in which it appears otherwise I want a different number, this is what i currently use.

matchRange = Application.Match("GRIEV", Range("'Reason Report'!A:A"), False)

but if griev does not appear, i get an error message, is this another way to do this without using error handling?

Thanks in advance for any help you can give!

Willy

--

Dave Peterson

Pivot Tables: counting columns and matching
 

Sorry, I don't use FoxPro.

Maybe a foxpro user can jump in.

Bob wrote:

Dave, I am having retrieving data from an external source, MS Fox Pro. I can
not get a driver to appear in the data type selection box?

How can I fix this?

Bob

"Dave Peterson" wrote:

Maybe something like:

Dim myPT As PivotTable
Set myPT = ActiveSheet.PivotTables(1)
MsgBox myPT.TableRange1.Columns.Count

And application.match seems ok to me.

You could use

dim foundcell as range
with activesheet
set foundcell = .range("a:a").find(what:="griev", ....)
end with
if foundcell is nothing then
'not there
else
'is there
end fi

but it seems pretty much the same to me.

Willy wrote:

Hey! I have two questions.

I'm trying to count the columns on a sheet to insert dynamic formulas and copy them accross to all my rows being used. This is the current formula I'm using to get my column count:

Dim Lcol

Set rng = ActiveSheet.UsedRange

Lcol = (rng.Columns(rng.Columns.Count).Column)

MsgBox Lcol

i'm trying to count the columns being used in the piviot table because the above formula includes the header. My piviot table will always be a min. of 3 columns, but with the header, the count will always be a min. of 6 columns. Is there any way that I can count the columns in the pivot table instead?

My second question is this, in my pivot table I have a heading named greiv, if greiv appears I want the row number in which it appears otherwise I want a different number, this is what i currently use.

matchRange = Application.Match("GRIEV", Range("'Reason Report'!A:A"), False)

but if griev does not appear, i get an error message, is this another way to do this without using error handling?

Thanks in advance for any help you can give!

Willy

--

Dave Peterson



--

Dave Peterson