Learn Excel VBA By Index (G)

GoTo

When the GoTo syntax is use, you can make Excel VBA to jump to a label and execute the line of code under the label like the On Error GoTo example I've demonstrate above. Let's look at another example...

Sub GotoDemo ()

x = Int(Rnd() * (1 - 10) + 10) 'a random number between 1 and 10

If x <>

GoTo Less

Else

GoTo More

End If

Exit Sub

Less:

MsgBox x & " is less than 5"

Exit Sub

More:

MsgBox x & " is more than 5"

End Sub

You can also specify a Excel macro to go to a specific range by using the GoTo method. For example, in a worksheet you have name a Range ("Credit Card"). To select this Range use the following statement...

Sub GoToDemo2 ()
Application.Goto Reference:="Credit Card" 'equivalent to Range("Credit Card").Select
End Sub



Goto Reference cell

When you want to go to a specific cell or range, you can instruct Excel to do so by entering the code below...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.Goto Reference:=Worksheets("Loan").Range("B100"), _
Scroll:=True

End Sub

As the Worksheet ("Loan") is activated, cell B100 will be at the top right window.

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