VBA Open Cell Reference using Find on text in Column and Row

0

I've spent the time to learn through the code used to Search Rows by name "POLRES" with a static column (I") was used as my example. And then to rename the sheet. 

As you expand it becomes more difficult. In the last example, The Column address with the text "Process Total" can change from Column "I" to another column if another title pushes it over to say "J" - Having a static column "I" will cause future issues i've found. 

Is there a way to search both Column & Row and show detail on that specific location. In this example, I'm trying to (.showdetail) on "Grand Total" in column (C:D) and then also Column "M" but since this location can change if new data is introduced, is there a way to search both "Grand Total" and "Question" and then open a pivot with (.showdetail) - I've attempted to follow all the steps required but I'm not sure I can do it separately like I'm attempting. - I've bolded my problem area with any changes I made)

Code_Goes_Here
Sub Open_OPEN_PHS_TOTAL()
Dim Pfind As Object
Dim Popen As Range
Dim TotalSheets As Integer
Dim TestSheetNum As Integer
Dim TestSheetName As String
Dim NewSheet As String
Dim SheetFound As Boolean
With Sheets("Master Report").Columns("C:D")
  Set Pfind = .Find(What:="PHS 3.1 Total", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
Range("I" & Pfind.Row).ShowDetail = True
Else
    MsgBox "Not Found"
End If
Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "3.1 SLA"
Sheets("Master Report").Select
With Sheets("Master Report").Columns("C:D")
  Set Pfind = .Find(What:="PHS 3.2 Total", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
Range("I" & Pfind.Row).ShowDetail = True
Else
    MsgBox "Not Found"
End If
Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "3.2 SLA"
Sheets("Master Report").Select
With Sheets("Master Report").Columns("C:D")
  Set Pfind = .Find(What:="PHS 3.3 Total", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
Range("I" & Pfind.Row).ShowDetail = True
Else
    MsgBox "Not Found"
End If
Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "3.3 SLA"
Sheets("Master Report").Select
With Sheets("Master Report").Columns("E:AL")
  Set Pfind = .Find(What:="Grand Total", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
 With Sheets("Master Report").Columns("E:AL")
  Set Popen = .Find(What:="QUESTION", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
'''''Problem Area
Range("Popen" & Pfind.Row).ShowDetail = True
Else
    MsgBox "Not Found"
End If
Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "Question Que"
End Sub
Answer
Discuss

Discussion

Please modify your post to simplify copying your code. In Edit mode, select the code and clock on the CODE button in the header of the posting screen. This will insert HTML code tags into your text which cause the code to be properly formatted (without extra spacing which otherwise has to be removed manually) and to be published with a Select button which copies all of it to the reader's clipboard.
I just spent 15 minutes just copying and formatting your code and, frankly, that just about exhausts my willingness to help you.
Variatus (rep: 2743) Jun 1, '19 at 10:13 pm
Fixed the issue. 9/10 i've never had an issue with formatting, before every time i've entered code it would auto generate that formatting box. Noted on how to fix that issue though. Thank you. My apologies. Always learning.
Sroncey21 (rep: 56) Jun 1, '19 at 10:28 pm
Don: Are you aware that your code tags cause double line spacing for code inserted between them in edit mode? I blank line is inserted after each line of code, at least in the "Answer"  field. Coming to think of it, the extra blank lines might be inserted whenever code is pasted, irrespective of the presence of code tags (perhaps a function of the CR in VBA code or the indent). The extra lines disappear when the post is uploaded and reappear when the post is edited.
Variatus (rep: 2743) Jun 4, '19 at 11:54 pm
Add to Discussion

Answers

0

The ShowDetail method requires Range("I" & Pfind.Row) to be in a pivot table. Your code obscures the nature of this sheet, first by using the Sheet object (which could be any kind of sheet, including a pivot table) instead of Worksheet, then by not specifying any sheet on which to find Range("I" & Pfind.Row) (which means VBA will assume the ActiveSheet) and, finally, by constantly changing the ActiveSheet in your code. Last, not least, your sample workbook doesn't contain any of the "Sheets" the code renames. 

Note that you can access any sheet with VBA without selecting it. Activate and Select are needed for the user interface only. VBA doesn't need either.

I have commented your code and partially re-written it, guessing at its purpose. You now have a function called GetSheet which returns a worksheet by a given name. If the sheet doesn't exist it is created. There is also a function called FindRow. It takes a search criterium and a range as parameters. If the criterium isn't found in the range the returned row number is zero. If the function returns zero no action is taken. Basically, you just need to call these two functions again and again to replace you existing code. That is simpler and therefore avoids the kind of errors your present code contains.

Sub Open_OPEN_PHS_TOTAL()
    ' 02 Jun 2019

    Dim Master As Worksheet
    Dim Ws As Worksheet
    Dim R As Long

    Set Master = Worksheets("Master Report")
    R = FindRow("PHS 3.1 Total", Master.Columns("C:D"))
    If R Then
        Set Ws = GetSheet("3.3 SLA")
        Ws.Cells(R, 8).ShowDetail = True
    End If

'    Dim Pfind As Object
'    Dim Popen As Range
'    Dim TotalSheets As Integer
'    Dim TestSheetNum As Integer
'    Dim TestSheetName As String
'    Dim NewSheet As String
'    Dim SheetFound As Boolean

'    With Sheets("Master Report").Columns("C:D")
'      Set Pfind = .Find(What:="PHS 3.1 Total", _
'                        LookIn:=xlFormulas, _
'                        LookAt:=xlPart, _
'                        SearchOrder:=xlByRows, _
'                        SearchDirection:=xlNext, _
'                        MatchCase:=False, _
'                        SearchFormat:=False)
'    End With
'
'    If Not Pfind Is Nothing Then
'        Range("I" & Pfind.Row).ShowDetail = True       ' which sheet is this range on?
'    Else
'        MsgBox "Not Found"
'    End If
'
'    Sheets("Sheet1").Select                            ' don't select any worksheet!
'    Sheets("Sheet1").Name = "3.1 SLA"
'    Sheets("Master Report").Select
'
'    With Sheets("Master Report").Columns("C:D")
'      Set Pfind = .Find(What:="PHS 3.2 Total", _
'                        LookIn:=xlFormulas, _
'                        LookAt:=xlPart, _
'                        SearchOrder:=xlByRows, _
'                        SearchDirection:=xlNext, _
'                        MatchCase:=False, _
'                        SearchFormat:=False)
'    End With
'
'    If Not Pfind Is Nothing Then
'        Range("I" & Pfind.Row).ShowDetail = True
'    Else
'        MsgBox "Not Found"
'    End If
'
'    Sheets("Sheet2").Select
'    Sheets("Sheet2").Name = "3.2 SLA"
'    Sheets("Master Report").Select
'
'    With Sheets("Master Report").Columns("C:D")
'      Set Pfind = .Find(What:="PHS 3.3 Total", _
'                        LookIn:=xlFormulas, _
'                        LookAt:=xlPart, _
'                        SearchOrder:=xlByRows, _
'                        SearchDirection:=xlNext, _
'                        MatchCase:=False, _
'                        SearchFormat:=False)
'    End With
'
'    If Not Pfind Is Nothing Then
'        Range("I" & Pfind.Row).ShowDetail = True
'    Else
'        MsgBox "Not Found"
'    End If
'
'    Sheets("Sheet3").Select
'    Sheets("Sheet3").Name = "3.3 SLA"
'    Sheets("Master Report").Select
'
'    With Sheets("Master Report").Columns("E:AL")
'      Set Pfind = .Find(What:="Grand Total", _
'                        LookIn:=xlFormulas, _
'                        LookAt:=xlPart, _
'                        SearchOrder:=xlByRows, _
'                        SearchDirection:=xlNext, _
'                        MatchCase:=False, _
'                        SearchFormat:=False)
    ' missing End With

'    With Sheets("Master Report").Columns("E:AL")
'
'    Set Popen = .Find(What:="QUESTION", LookIn:=xlFormulas, LookAt _
'            :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
'            False, SearchFormat:=False)
'    End With
'
'    If Not Pfind Is Nothing Then
'        '''''Problem Area
'        Range("Popen" & Pfind.Row).ShowDetail = True
'    Else
'        MsgBox "Not Found"
'    End If
'
'    Sheets("Sheet4").Select
'    Sheets("Sheet4").Name = "Question Que"
End Sub

Private Function FindRow(FindWhat As Variant, _
                         Rng As Range) As Long
    Dim Fnd As Range

    Set Fnd = Rng.Find(What:=FindWhat, _
                       LookIn:=xlFormulas, _
                       LookAt:=xlPart, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False, _
                       SearchFormat:=False)

    If Fnd Is Nothing Then
        MsgBox "Not Found"
    Else
        FindRow = Fnd.Row
    End If
End Function

Private Function GetSheet(ByVal SheetName As String) As Worksheet
    ' 02 Jun 2019

    Dim Ws As Worksheet

    Set Ws = ActiveSheet                    ' remember the ActiveSheet
    Application.ScreenUpdating = False
    On Error Resume Next
    Set GetSheet = Worksheets(SheetName)    ' error if sheet doesn't exist
    If Err Then
        ' create the sheet
        Set GetSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        ActiveSheet.Name = SheetName
        Ws.Activate                         ' re-activate the original ActiveSheet
    End If
    Application.ScreenUpdating = True
End Function

Regrettably, my code doesn't directly address your question. It isn't possible to do that based on the workbook information your have provided. I hope that by replacing your repetitive code with function calls the problem might disappear by itself.

Discuss

Discussion

"UPDATE - 1 of 3"

Thank you so much for all the help on renaming sheets! 

 which sheet is this range on? I've found that since the file opens by default on "Master Report" and is only for my purpose I will always start out correct when starting my macro. But I could have it select the sheet just to ensure accuracy. 

Renaming the sheets was only a secondary goal. Which i am so, so grateful for that knowledge to use and now have. My goal is now that I can search and open a cell's pivot with .showdetail function based on one search of the column name "PHS 3.1 Total" and a static column "I"
Sroncey21 (rep: 56) Jun 2, '19 at 12:24 am
"UPDATE - 2 of 3"

I could unlock so many possiblities if I was able to use the Find feature on both Column "PHS 3.1 Total" but instead of Column "I" that a find on "Process Total" could be used. This worksheet example would open cell "I64" - The reason this so very important is that if a category has 0 it will not be on a future sheet and will throw off a row, and or column by 1. So having a static "I64" tomorrow the new report could move up one and only hold 63 total rows and "I64" would be a blank. 

I know this is probably a very difficult task but I've tried every possible code I know (still learning) as well as googled and youtubed every variation of this solution. There are parts of this code that are very straight forward but parts that throw me and as you know if even one part is wrong it doesn't work. 
Sroncey21 (rep: 56) Jun 2, '19 at 12:24 am
Again. I appreciate all that you do. I try to keep my posts short and concise as i can to not take up too much of your time. If I didn't care I would just give up, but i'm very persistent. Hopefully this is possible.

There are 5 Targets on cell References I need to open a pivot. (I can rename the sheets, just not sure if it is possible to do this like you said)

1. Column Reference "Process Total" - Row reference "PHS 3.1 Total"

2. Column Reference "Process Total" - Row reference "PHS 3.2 Total"

3. Column Reference "Process Total" - Row reference "PHS 3.3 Total"

4. Column Reference "QUESTION" - Row reference "Grand Total"

5. Column Reference "UNDER" - Row reference "Grand Total"
Sroncey21 (rep: 56) Jun 2, '19 at 12:25 am
I doubt that your wishes can be addressed in this thread. I hate it if the question doesn't become apparent until the third paragraph of the discussion. Plese consider formulating a new question.
What is needed now is a workbook with the existing code in it - after incorporating the code I supplied here. The workbook must include the pivot tables the code is trying to update so that I can run tests.
Note that the code line Ws.Cells(R, 8).ShowDetail = True could be replaced with Dim C as Long: C = FindColumn("Process Total", Master.Rows(3)): If C Then Ws.Cells(R, 8).ShowDetail = True Else Exit Sub End If, where FindColumn is an exact copy of FindRow but returning Fnd.Column instead of Fnd.Row.
Variatus (rep: 2743) Jun 2, '19 at 1:16 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login