Find Text Based On Active Cell Contents

pukks · May 11th 2007

Hi Forum

I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A.

I did record a macro but it did not work.
many thanks
pukks:confused:

Delta-101 · May 11th 2007

Re: Find Text Based On Active Cell Contents

Hi,

I am not an expert, but here is my solution:

VBA:

Code

Sub Find_Selected()
cellval = ActiveCell.Value
With Worksheets("Sheet2").Range("a1:d6")
Set c = .Find(cellval, LookIn:=xlValues)
If Not c Is Nothing Then
foundcell = c.Address
' do what you need to do
End If
End With
End Sub

Display More

'assign macro button to "Find_Selected" & change the range values and sheet name to suit,
'foundcell returns cell address where value is found on sheet2

parrimin · May 11th 2007

Re: Find Text Based On Active Cell Contents

You have to put a commandbutton. Then right click->view code on the created button, and then you will see the code windows, and some code that says:

Code

Private Sub CommandButton1_Click()
End Sub

You only have to put in this Sub whatever you want to do. For example

Code

Private Sub CommandButton1_Click()
Dim value As String 'Declare a string
value = ActiveCell.value 'Get the value of the selected Cell
Dim ws As Worksheet
'ws is the worksheet from we are searching the value
'You have to change myWorkSheetName for you worksheet name
Set ws = ThisWorkbook.Worksheets("myWorkSheetName")
ws.Activate
Dim c As Range 'Declare a cell
Set c = ws.Cells.Find(value, LookIn:=xlValues) 'Search the value
If Not c Is Nothing Then 'If value found
c.Activate 'Activate the cell, select it
Else
MsgBox "Not found" 'shows a message "Not Found"
End If
End Sub

Display More

pukks · May 11th 2007

Re: Find Text Based On Active Cell Contents

Thanks Guys, Works a treat.:)

pukks

mbarillaro · Mar 13th 2020

The last set of code posted does not seem to work when you have a pivot table with duplicate values. The sourceData just selected the first value. It does not look across the pivot tables row and columns to select the value that correspond to those dimensions. Does anyone have code that takes that into account?

**royUK** · Mar 13th 2020

MB, welcome to Ozgrid.

Please take a few minutes to read the Forum Rules. One of them asks you not to post in another person's Thread, please start your own and add a link to this one if you think it ill help.

Find Text Based On Active Cell Contents

royUK Mar 13th 2020

Share