PDA

View Full Version : Introductions - New to VBA Express - Beginner at VBA Code



SamMurr
07-21-2018, 04:28 PM
Can someone help direct me to where I might figure out how to use VBA to search for text within the current section header?

Thanks in Advance!

Sam Mur

Logit
07-21-2018, 06:43 PM
.
Well .. there are different methods of searching. You can search an entire sheet ... the entire workbook (all tabs) ... just one column on one sheet ... several columns on several sheets.

It all depends on what and how you want to search.

This macro will search SHEET 1, entire sheet, for the term you enter in the InputBox when it appears.



Option Explicit


Sub FindInLists()
'
' Select FindInLists Macro
'

Dim SheetsToSearch, SrchStrg As String, ws As Excel.Worksheet, r As Range
SheetsToSearch = Array("Sheet1") '// Enter the exact sheet names of sheets to be searched

SrchStrg = Application.InputBox("Enter Term to search ", "Search Term", Type:=2)

For Each ws In ThisWorkbook.Sheets
If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
With ws.Range("A6:P6067") 'EDIT RANGE AS REQUIRED
Set r = .Find(what:=SrchStrg, After:=.Range("A1")) 'find the cell whose value is equal to SrchStrg and activate it
If Not r Is Nothing Then
ws.Activate: r.Activate

'PUT YOUR CODE HERE TO WRITE THE TICKER DATA SOMEWHERE FOR REVIEW

ElseIf r Is Nothing Then
MsgBox "Search term does not exist. ", vbInformation, "Item Not Found"
End If
End With
End If
Next

End Sub