VBA Range Method - Find

Range find is to finds specific information in a range.

Syntax : Range.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Returns : Not xxx Is Nothing(True)/Nothing(False)

Under find we have to specify What is Required and the rest is optional but for me LookIn and LookAt also importance.
LookIn:=xlFormulas, xlValues, xlComments, or xlCommentsThreaded
LookAt:=xlWhole or xlPart

If the information found then we can get the 1st location only base on search direction for example row and column index. If we need to find next location then we have to combined with FindNext.

Below example will loop keyword in activesheet at column B and find this keyword in reference sheet at column E and get additional information at B and G.

VBA Vode:

Option Explicit
Sub FindMyBlogLink()

    Dim LstRow As Long
    Dim i As Integer, j As Integer
    Dim ActSht As Worksheet, RefSht As Worksheet
    Dim FndRng As Range, FndKey As Range
    Dim StrKey As String

    'To start with active sheet
    Set ActSht = ActiveSheet
    
    'To check reference sheet available or not?
    For j = 1 To Sheets.Count
        If Sheets(j).Name = "mrvba" Then
            Set RefSht = Sheets("mrvba")
        End If
    Next j
    
    'If reference sheet exist then start searching for keyword
    If Not RefSht Is Nothing Then
        Set FndRng = RefSht.Columns("E:E")
        LstRow = ActSht.Cells.Find("*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row + 1
        i = 2
        Do
            StrKey = ActSht.Range("B" & i)
            Set FndKey = FndRng.Find(StrKey, LookAt:=xlWhole)
'            Set FndKey = FndRng.Find(StrKey, LookAt:=xlPart)
            If Not FndKey Is Nothing Then
                ActSht.Range("C" & i) = RefSht.Cells(FndKey.Row, 2)
                ActSht.Range("D" & i) = RefSht.Cells(FndKey.Row, 7)
            End If
            StrKey = ""
            Set FndKey = Nothing
            i = i + 1
        Loop Until i = LstRow
    'Exit with mesaage if reference sheet not exist.
    Else
        MsgBox "Sorry! Reference sheet name mrvba not found."
        Exit Sub
    End If
End Sub

VBA Vode: (Sample from Microsoft with FindNext)

Sub FindValue()
    
    Dim c As Range
    Dim firstAddress As String
    
    'To find number 2 in replace with 5
    With ActiveSheet.Range("A1:A500")
        Set c = .Find(2, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = 5
                Debug.Print c.Address
                Debug.Print c.Row
                Debug.Print c.Column
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With
    
End Sub

Note:
By using range find is much more faster than loop entire sheet looking for keyword match.

Read more about Excel.range.find, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Excel.range.find
Other Reference-Find-and-replace-extensibility

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Public

Public statement is to declare public variables and allocate storage space at module level.Instead of using Dim Statement we can use Public Statement. The different is Dim Statement declare inside the Sub procedure and Public Statement on top of module follow by Sub procedures. The variables declare by using Public statement can be use by all Sub procedures in all modules.

Syntax : Public Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Public and Variable name, the rest is same as Dim Statement and Private Statement.

Note:
The only different between Private and Public statement is Public statement are available to all procedures in all modules but Private statement is limited to procedures inside specific module where variables is declare.

Read more about Public statement, excel training beginners, coding in vba,
excel training online, visual basic for applications at below links.

Microsoft Reference-Public-statement
Other Reference-Lifetime-scope-global-level

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Private

Private statement is to declare private variables and allocate storage space in module level. Instead of using Dim Statement we can use Private Statement. The different is Dim Statement declare inside the Sub procedure and Private Statement on top of module follow by Sub procedures. The variables declare using Private statement can be use by all Sub procedures inside the module where this variables are declares. This variables can't use outside the declare module.

Syntax : Private Variable Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Private and Variable name, the rest is same as Dim Statement.

VBA Vode:

Option Explicit
Private MyVarA As String, MyVarB As String
Sub Macro1()
    MyVarA = "My Country"
    myVarB = "Malaysia"
    Debug.Print MyVarA, myVarB
End Sub
Sub Macro2()
    Debug.Print MyVarA, myVarB
End Sub

Note:
For the above example we have 2 variables MyVarA and MyVarB, we have 2 Sub procedures which is Macro1 and Macro2. If we run Macro1 then follow by Macro2 by using the same variables then we get the same answer unless we reset these variables each time before exit Sub procedures.

Read more about Private statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Private-statement
Other Reference-Lifetime-scope-module-level

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Sub, Private Sub, Public Sub

Sub statement is subroutine also known as Procedures or Macros (When we record macro the default name is Sub Macro1() follow by series of code and close with End Sub. Macro1 is the default name of procedure recorded. Same case when we create on our own.we start with Sub Procedure Name() and Close with End Sub.

Syntax :
Sub Procedure Name (ArgList)
    Statements (Code)
    Exit Sub
    Statements (Code)
End Sub

Actually the syntax consist many optional parts but required only 3 which is Sub, Procedure name and End Sub to explain in detail refer below:

Case Study 1: 

Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This is the most typical of Sub Statement, it can be accessible from anywhere in the project and listed in macros and can run directly.

Case Study 2: 

Sub Procedure Name (ByVal and ByRef Variables)
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement only can be access from another Sub Statement in the project because we need to specify variables inside open and close bracket and not listed in macros.

Case Study 3: 

Public Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement same as typical Sub Statement above, it can be accessible from anywhere in the project and listed in macros and can run directly.

Case Study 4: 

Private Sub Procedure Name ()
    Statements (Code)
    Call another Procedures
    Exit Sub
    Statements (Code)
    Call another Procedures
End Sub

This Sub Statement only can be access from another Sub Statement within the same module and not listed in macros.

Note:

1. There are another Sub which Friend Sub (only class module) and Static Sub (preserved variables) but seldom use for beginner.
2. The procedure name must be related with the macro task to ease accessible process and there are rules to follow ex. no space, certain characters and etc.

Read more about Sub statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Sub-statement
Other Reference-Subroutines

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

VBA Statement - Redim and Redim Preserve

Redim statement is to reallocate storage space for dynamic array variables and declare at procedure level within your Sub and End Sub. Redim statement only can be use after we declare Dim Statement for array for example Dim MyVar() As String and data remain unchanged. Array number in bracket is not specify.

Syntax : Redim Variables Name As Data Type Or
               Redim Preserve Variables Name As Data Type

Actually the syntax consist many optional parts but required only 2 which is Redim and Variable name, to explain in detail refer below:

Case Study 1:
Dim myVar(5) As String
Lower Bound (LBound) control by Option Base 1 (Declare at module level) if present then LBound = 1 otherwise 0, Upper Bound (UBound) is 5. For this case we don't have to use ReDim statement because LBound and UBound already specify. If we try to use myVar(6) or more then Run time error '9' : Subscript out of range. If we try to resize by using Redim myVar(6) or more then Compile error: Array already dimensioned.

Case Study 2:
Dim myVar(5 to 10) As String
This case same as above except Lower bound already specify equal to 5 and Upper Bound equal to 10. Redim is not necessary.

Case Study 3:
Dim myVar() As String
This is refer to dynamic array and Redim Statement is compulsory. For example we already Redim myVar(1) and assigned the value, later we Redim myVar(2) and assigned the value. The myVar(1) value will be deleted or erase. To avoid this we must use Redim Preserve myVar(1) and Redim Preserve myVar(2). For Redim Preserve once we reverse the sequence the data also lost for the sub sequence number.

Note:
Redim Statement - Will erase all previous data.
Redim Preserve Statement - Will not erase previous data.

Read more about Redim statement, macro enabled excel, excel macro,
vba coding, vba code at below links.

Microsoft Reference-Redim-statement
Other Reference-Redim-statement
Other Reference-Redim-preserve

Leave your comments if you have any request.
Practice makes perfect.
Thank You.