CODEWITHSUNDEEP
excelvba
euphoricia
euphoricia

Reputation: 3

Find Matching Strings within a Table with Hidden Values

Sample table below (only 1 column):

-------------------------
-Floating Point Workload-
-Molecular Dynamics     -
-Boimedical Imaging     -
-Ray Tracing            -
-------------------------

Where some of the table is hidden depending on what's chosen in a slicer.

I'm looking to search for the visible values and, based on these, make parts of another PivotTable visible. Code so far below:

Sub FPWorkloads()

Dim tbl As ListObject
Dim Found1 As Range
Dim Found2 As Range
Dim Found3 As Range
Dim Found4 As Range
Dim Found5 As Range
Dim Found6 As Range
Dim Found7 As Range
Dim Found8 As Range
Dim Found9 As Range
Dim Found10 As Range
Dim Found11 As Range
Dim Found12 As Range
Dim Found13 As Range
Dim Lookup1 As String
Dim Lookup2 As String
Dim Lookup3 As String
Dim Lookup4 As String
Dim Lookup5 As String
Dim Lookup6 As String
Dim Lookup7 As String
Dim Lookup8 As String
Dim Lookup9 As String
Dim Lookup10 As String
Dim Lookup11 As String
Dim Lookup12 As String
Dim Lookup13 As String
Dim cellrange As Range

'Lookup Values
  Lookup1 = "Explosion Modeling"
  Lookup2 = "Physics: Relativity"
  Lookup3 = "Molecular Dynamics"
  Lookup4 = "Biomedical Imaging"
  Lookup5 = "Ray Tracing"
  Lookup6 = "Fluid Dynamics"
  Lookup7 = "Weather Forecasting"
  Lookup8 = "3D Rendering"
  Lookup9 = "Atmosphere Modeling"
  Lookup10 = "Climate Modeling"
  Lookup11 = "Ocean Modeling"
  Lookup12 = "Image Manipulation"
  Lookup13 = "Computational Electromagnetics"

'Store Table Object to a variable
  With Sheet6
  Set tbl = Sheet6.ListObjects("Table6")
  End With

'Attempt to find value in Table's first Column

  With Sheet6

On Error Resume Next

  Set Found1 = tbl.FoundCell.Columns(1).Find(Lookup1, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found2 = tbl.FoundCell.Columns(1).Find(Lookup2, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found3 = tbl.FoundCell.Columns(1).Find(Lookup3, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found4 = tbl.FoundCell.Columns(1).Find(Lookup4, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found5 = tbl.FoundCell.Columns(1).Find(Lookup5, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found6 = tbl.FoundCell.Columns(1).Find(Lookup6, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found7 = tbl.FoundCell.Columns(1).Find(Lookup7, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found8 = tbl.FoundCell.Columns(1).Find(Lookup8, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found9 = tbl.FoundCell.Columns(1).Find(Lookup9, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found10 = tbl.FoundCell.Columns(1).Find(Lookup10, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found11 = tbl.FoundCell.Columns(1).Find(Lookup11, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found12 = tbl.FoundCell.Columns(1).Find(Lookup12, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  Set Found13 = tbl.FoundCell.Columns(1).Find(Lookup13, LookIn:=xlValues, LookAt:=xlCellTypeVisible)
  
On Error GoTo 0

End With

With Sheet3.PivotTables("PivotTable1").PivotFields("Specific Workload")
  
  If Not Found1 Is Nothing Then
    .PivotItems("Average of 503 Base").Visible = True
    .PivotItems("Average of 503 Peak").Visible = True
    .PivotItems("Average of 603 Base").Visible = True
    .PivotItems("Average of 603 Peak").Visible = True

This is bringing all of the Foundx values back as 'Nothing' even when they are present in the table. Any ideas what's wrong?

Thanks

Upvotes: 0

Views: 27

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

The ListObject doesn't have FoundCell property. Also XlLookAt enumeration may be xlPart or xlWhole, not xlCellTypeVisible (https://learn.microsoft.com/ru-ru/office/vba/api/excel.xllookat). So errors are raising but On Error Resume Next suppresses them. This causes the Foundx variables to remain Nothing.

Try next code:

Set Found1 = tbl.DataBodyRange.Columns(1).Find(Lookup1, LookIn:=xlValues, LookAt:=xlWhole)

Upvotes: 1

Related Questions