Learn Excel VBA By Index (F)

Fill Method

You can use the Fill method to fills a range of cells. Let's see how this is implemented below.

Sub FillDemo ( )

Range("A2:A10").FillUp
'this will fill up the value contain in cell A10 to all cell above until A2

Range("A2:A10").FillDown
'this will fill down the value contain in cell A2 to all cell below until A10

End Sub



Find a File

In order to search for a file in your system, you can use the FileSearch object. The macro below will do the trick.

Sub FileSearchDemo ( )

Dim PathInfo As String

With Application.FileSearch

.NewSearch

.FileName = “Computer.xls”

End With

End Sub

You must make that such file exist, else Excel VBA will return an error message.



Font Object

Properties of this object includes Bold, Size, Italic, Underline and etc. To set the font properties you can enter the code as follow…

Sub FontDemo ( )

Set SelFont = ActiveSheet.Range(“A1”).Font

SelFont.Font.Size = 12

SelFont.Font.Bold = True

End Sub

The above procedure enables you to set the font of cell A1 to size 12 and bold.



For Next Loop

Use this syntax when you want to execute for a determine number of time.

Sub ForNextDemo1 ()

For x = 1 to 10

y = x + 1

Next x

MsgBox (y)

End Sub

Here you'll get y = 11

Look at another example...The code below will for cell value that are positive to bold.

Sub ForNextDemo2 ()

Set MyRange = Range("A1:A100")

y = MyRange.Rows.Count ' y = 100

For x = 1 to y

If MyRange.Cells(x).Value > 0 Then

MyRange.Cells(x).Font.Bold = True

End If

Next x

End Sub

Or you can use the For Each - Next syntax to do the same thing.

Sub ForNextDemo3 ()

Set MyRange = Range("A1:A100")

For Each cell in MyRange

If cell.Value > 0 Then

cell.Font.Bold = True

End if

Next cell

End Sub