Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Search Column name with VBA code & Open Cell

0

Been trying to look up VBA code to search a specific column name "Process Total" in "column I" of tab name "AWD List" based on Column D name "POLRES"

Is there a way to find that "Process Total" row in Column I that corresponds to Column B name "POLRES" and then have it open that cell in "Column I" into a new sheet named "POLRES"

I've been working with part of the code, but cannot find anything that shows how to find and open the cell I need.

Sub Find_POLRES_ProcessTotal

.Find("Process Total", After:=Range("I:I"), MatchCase:=True)

[/CODE]

.Find("POLRES", After:=Range("B:B"), MatchCase:=True)

End Sub

[/CODE]

excelmacros-vba
MS Office Version: Sroncey21
posted by: Sroncey21 (rep: 66) on Mon May 20, 2019 at 4:27 pm
Answer
Discuss

Discussion

I atttached my worksheet updated after running the VBA code. It appears to be finding POLRES, naming the tab but it appears to just be entering what the total number is in the tab. 

Is there a way to get the pivot table to open in this POLRES tab (just like if I was to double click that number)
Sroncey21 (rep: 66) May 21, '19 at 10:28 am
Sorry can't help there. I do not use Pivot tables. You may need to post a new question to get this last bit done.
k1w1sm (rep: 197) May 21, '19 at 4:46 pm
Add to Discussion

Answers

0

Here is some code that does stuff. 


Sub OpenPolresTotal()
'
' K1w1 21/5/19

Dim Pfind As Object
Dim Ptotal
Dim TotalSheets As Integer
Dim TestSheetNum As Integer
Dim TestSheetName As String
Dim NewSheet As String
Dim SheetFound As Boolean

With Sheets("AWD List").Columns("D")
  Set Pfind = .Find(What:="polres", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

End With

If Not Pfind Is Nothing Then
   Ptotal = Range("I" & Pfind.Row).Value
Else
    MsgBox "not Found"
End If

'check to see if sheet exists
TotalSheets = Sheets.Count
SheetFound = False
For TestSheetNum = 1 To TotalSheets
    If Sheets(TestSheetNum).Name = "Polres" Then
        SheetFound = True
        Exit For
    End If
Next

 If Not SheetFound Then
    Sheets.Add.Name = "Polres"
    Sheets("Polres").Cells(1, 1) = Ptotal
 End If
End Sub
posted by: k1w1sm (rep: 197) on Mon May 20, 2019 at 8:33 pm
Discuss


Answer the Question

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