View Full Version : Macro to drill into pivot table
brennaboy
06-29-2011, 05:35 AM
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
06-29-2011, 10:26 AM
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
06-29-2011, 10:45 PM
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
06-30-2011, 02:49 AM
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
06-30-2011, 11:55 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.