What is the syntax to search a range?

Range("<your range here>").Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
'What - data to search for; only mandatory parameter
'value (ex) any VBA data type like 123, 12.3, "name"

'After - a single cell to start searching from
'value (ex) Range("A1"), Range("B5")

'LookIn - to search in formulas, values, or comments
'value (ex) xlValues, xlFormulas, xlComments

'LookAt - look at part of cell or entire cell?
'value (ex) xlWhole, xlPart

'SearchOrder - search by rows or columns
'value (ex) xlByRows, xlByColumns

'SearchDirection - search next cell or previous ones
'value (ex) xlNext, xlPrevious

'the following parameter's values are either true or false
'MatchCase - is search case sensitive?
'MatchByte - only relevant if you have installed double-byte language support
'SearchFormat - search by formatting (which is set using Application.FindFormat)

It is critical to note that the Range.Find method does not return a value, rather, it returns a Range object. If nothing is found, the Range object will be Nothing.

'examples

'returns Emily Carn
Range("B3:B20").Find(What:="Emily").Value

'selects cell B8
Range("B3:B20").Find(What:="Emily", MatchCase:=False).Select

'returns 19
Range("B3:B20").Find(What:="Emily", After:=Range("B17")).Row

capture

Advertisements

What is the syntax for a function to return a value?

Function <function name> (<parameter name> As <data type>)
     'parameters are optional
     <function name> = <your code/calculations here>
End Function
'examples
Function TotalPay (hrs As Integer)
     TotalPay = hrs*12
End Function

Public Function returnOne() As Integer
     returnOne = 1
End Function

 

What is the syntax to declare procedures?

Private Function <your name> ()
   ‘your code here
   ‘callable only by procedures in the same module
End Function


Public Function <your name> ()
   ‘callable from any procedure
End Function

 
Private Sub <your name> ()
   ‘callable only by procedures in the same module
   ‘cannot be called from Macros dialog box
End Sub

 
Public Sub <your name> ()
   ‘callable from any procedure of any module
   ‘can run procedure from Macros dialog box
End Sub

What is a procedure?

  • Grouped statements that relate to a specific task
  • Used for complex tasks that can be further broken down into smaller tasks
  • Two types of procedures: sub and function
Function <name>(<parameters>)
‘callable from anywhere in program
‘can return values
‘put function declarations in a module sheet
End Function
Sub <name>(<parameters>)
‘do not return values
End Sub