Macro to drill into pivot table

[brennaboy]

Hi,

I am writing a macro for a daily task and one of the steps is to drill into a pivot table cell.

However, the cell reference changes daily, so I need to tell it to drill into the cell of the intersection of the row starts with "Manager" and column starts with "Notes".

How would I do this?

Cheers,

Bren.

[mancubus]

try:


Sub FindIntersectionCell()
Dim rowFind As Long, colFind As Long
Dim cellFind As Range
rowFind = Columns(1).Find("Manager", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row
colFind = Rows(1).Find("Notes", Range("A1"), xlValues, xlWhole, xlByRows, xlNext).Column
Set cellFind = Cells(rowFind, colFind)
cellFind.Value = "This Cell"
End Sub

[mancubus]

try:


Sub FindIntersectionCell()
Dim rowFind As Long, colFind As Long
Dim cellFind As Range
rowFind = Columns(1).Find("Manager", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row
colFind = Rows(1).Find("Notes", Range("A1"), xlValues, xlWhole, xlByRows, xlNext).Column
Set cellFind = Cells(rowFind, colFind)
cellFind.Value = "This Cell"
End Sub



correction:

cellFind.Value = "This Cell"

is just an example. the found cell being in pivot table, we can't change its value.


perhaps

MsgBox cellFind.Value

would be a better example.

[brennaboy]

I tried this and it does not seem to work. In the debugger, it is giving me a run-time error 13 type mismatch at the line "rowFind = Columns(4).Find("NOTE", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row"

Any ideas?

[mancubus]

if it is "NOTE" (post#1 it is "Manager") and at column 4
try

rowFind = Columns(4).Find("NOTE", Range("D1"), xlValues, xlWhole, xlByColumns, xlNext).Row


or


rowFind = Columns(4).Find("NOTE", Cells(1, 4), xlValues, xlWhole, xlByColumns, xlNext).Row