Msgbox to output cell value in adjacent column but pivot tables won't let me...

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,961
Office Version
  1. 2019
Platform
  1. Windows
Hello my dear members,

How do I output the value in a cell into a message box? Column reference of that cell is column F and Row reference is that of a sheet changed row in column B.

But the problem is that I am dealing with pivot tables. Pivot table will always comprise of two cells adjacent to one another. In this case it is Cell A5 and Cell B5.
A5 = Report Filter Label
B5 = Report Filter Values

Now if I hadn't have the pivot tables the following would have worked just fine..

VBA Code:
If Not Intersect(Target, Range("B5:B100")) Is Nothing Then
MsgBox "Your result is " & Range(Target.Address).Offset(0, 5).Value

However when the target address in column B is not just a normal cell but a pivot table dropdown. This vba won't work and output me type mismatch error
To output the Report Filter Value itself the following line will do it for me..

VBA Code:
MsgBox "Your result is " & Target.Offset(0, 1).PivotItem.Value

So this will output me message box = Your result is "Value in B5 which is a pivot filter value"
But I want the value in F5 instead which is a non pivot filter value and just a normal cell.

Will appreciate.
Anyone?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,961
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
MsgBox "Your result is " & Range(Target.Address).Range("F1").Value

Got it..
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top