Find function

Think you have a spread sheet similar to this. It contains value "100" in several cells.

So today I will teach you how to get the address of those cells from VBA code

First let's define the value we need to search.

Dim s1 As String

Then assign the value we search to that variable.

s1 = "100"

And it is a good practice to use objects where you can. It prevent occurrence of errors. Think you have worksheet call "Report". If you use conventional way you need to refer that sheet by worksheets("Report") at every where you needed to refer it. But if you use worksheet object you can refer it from very short name. So this will give neat look to your coding as well. So I prefer using objects. So use worksheet objects as follows.

Dim WS As Worksheet Set WS = Worksheets("sheet1")

Then Set the range we need to search.

With WS.Range("A1:T23")

Following line of code will give you the address of each cell having 100 in it.

MsgBox c.Address

Loop will continue until it find a value for c except first value.

Loop While Not c Is Nothing And c.Address <> firstAddress

So here is the full code. You can use this wherever you need by changing the sheet names, range and search values accordingly. Also you can pass the search value as a variable.

Dim s1 As String s1 = "100" Dim WS As Worksheet Set WS = Worksheets("sheet1") With WS.Range("A1:T23")     Set c = .Find(s1)     If Not c Is Nothing Then         firstAddress = c.Address         Do             MsgBox c.Address             Set c = .FindNext(c)         Loop While Not c Is Nothing And c.Address <> firstAddress     End If End With

With the above code you will get the cell address of cells having "100" to msgboxes.

Populate combo box with sheet name of opened work book

Here cboFromSheeet is the name of the combo box.

Dim wbFrom As Workbook
Set wbFrom = Application.Workbooks.Open(lblFromFilePath.Caption)
Dim ws As Worksheet

'Clear the combo box
cboFromSheeet.Clear

With cboFromSheeet
    For Each ws In Worksheets
            .AddItem ws.Name
    Next ws
End With

cboFromSheeet.Text = "Select a Sheet"

Add work sheet with a name

Think you want to add a worksheet with a name "Report"

This code will do it.

Sheets.Add.Name = "Report"

However you can do this even with objects

See following code

Dim WS as Worksheet
Set WS = Sheets.Add

You can refer this new sheet by WS wherever at the code. You don't need to know anything about like it's name, location etc. Even you can give it a name later.

For an example think you want to name it as "Calculation". So below code will do it.

WS.Name = "Calculation"

Find last row having data in a certain column

    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

reference workbooks and sheets Explicitly

This example shows how to reference workbooks and sheets explicitly.

Dim wbFrom As Workbook
Dim wbTo As Workbook
Dim wsFrom As Worksheet
Dim wsTo As Worksheet
Dim Str1, Str2, s1, s2, s3, s4 As String
'paths of 2 workbooks
Str1 = lblFromFilePath.Caption
Str2 = lblToFilePath.Caption
Dim WrdArray() As String
WrdArray() = Split(Str1, "\")
's1 and s2 are Workbook names
s1 = WrdArray(UBound(WrdArray()))
WrdArray() = Split(Str2, "\")
s2 = WrdArray(UBound(WrdArray()))
'Worksheet names
s3 = cboFromSheeet.Value
s4 = cboToSheet.Value

Set wbFrom = Workbooks(s1)
Set wbTo = Workbooks(s2)
Set wsFrom = wbFrom.Worksheets(s3)
Set wsTo = wbTo.Worksheets(s4)

If cboFromSheeet.Text <> "Select a Sheet" And cboToSheet.Text <> "Select a Sheet" Then
    MsgBox wsFrom.Name, , "from"
    MsgBox wsTo.Name, , "To"
Else
    MsgBox "Please select relevant sheets from the drop down menu.", vbOKOnly, "WARNING!"
End If

Browse File And Assign Full Path And Name Into Two Variables

This Excel VBA tutorial explains how to use file browsers in Excel VBA applications. In Excel VBA applications, we often need to let the user input data from various files to the application. If we know the file path then we can directly use that path in the code to open the file. But this method is not practicable because we can’t ask the users to open the VBA editor and change the codes. Also it will be impossible if you lock the source code. Therefore using a file browser is the ideal solution for that kind of requirement.

If the application has a file browser option like above, then users can easily select files to upload the data. So let’s look at how to add a file browser option to a VBA application. Let’s name this subroutine as “SelectFile”

Sub SelectFile()

End Sub

First we need to declare a few variables.

Dim strFile As String
Dim FullPath As String
Dim FileName As String
Dim WrdArray() As String

Next we can show the “Open dialog box”. So the user can select a file.

sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")

Now we need to check whether the user clicked on the “Open” button or the “Cancel” button. We can use a If .. Then .. Else Statement to determine that.

If sFile = "False" Then
     'If user click "Cancel" button
Else

End If

If the user clicks on the “Cancel” button then we should exit the subroutine using “Exit Sub” statement. Otherwise the program will be executed to the file opening section and produce an error. If the user clicks on the “Open” button, sFile will contain the full path of the file. So we can assign it to the FullPath variable as follows.

FullPath = sFile

Now the remaining task is to extract the name of the file from the sFile variable. We can use the Split function to get that job done. Name of the file will occur after the last “\” character. So we can split the sFile string using “\” as a delimiter and then get the last element of the result array.

WrdArray() = Split(sFile, "\")
FileName = WrdArray(UBound(WrdArray()))

Want to learn about Split function? Read this post => Split Function

Now complete If .. Then .. Else block should look like this.

If sFile = "False" Then
     'If user click "Cancel" button
     Exit Sub
Else
     FullPath = sFile
     WrdArray() = Split(sFile, "\")
     FileName = WrdArray(UBound(WrdArray()))
End If

Finally we can open the file and assign it to the WB variable as follows.

Set WB = Workbooks.Open(FullPath)

Note that the full path is enough to open the file. But knowing the file name can be useful for some other purposes. Below is the full code for the “SelectFile” subroutine.

Sub SelectFile()

    Dim WB As Workbook
    Dim strFile As String
    Dim FullPath As String
    Dim FileName As String
    Dim WrdArray() As String

    sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")

    If sFile = "False" Then
         'If user click "Cancel" button
         Exit Sub
    Else
         FullPath = sFile
         WrdArray() = Split(sFile, "\")
         FileName = WrdArray(UBound(WrdArray()))
    End If

    Set WB = Workbooks.Open(FullPath)

End Sub

In the above example we let the user to only select the Excel files. If you want to allow the user to select any file type then replace “sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")” with below line.

sFile = Application.GetOpenFilename()

And sometimes you may want to permit users to only select CSV files. Use the below code in that case.

sFile = Application.GetOpenFilename("CSV files,*.csv")

In this lesson we considered selecting only one file. But you can use the “GetOpenFilename” method to select multiple files as well. But then it will return an array (array will contain names of the selected files.) instead of a string.

Open a closed work book

Application.Workbooks.Open ("E:\Blogger Folder\My Files\Folder1\Calculations.xls")