excelpaidjo: September 2015

How to Use Cell in Excel VBA

Worksheet.cells property returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).
Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword.
Example of using cells:

Sub Sample1()
Dim i As Integer

For i = 1 To 10
    Worksheets("Sheet1").Cells(i, 1).Font.Size = 12
    Worksheets("Sheet1").Cells(i, 1).Offset(0, 1).Value = Worksheets("Sheet1").Cells(i, 1).Value
    Worksheets("Sheet1").Cells(i, 1).Offset(0, 1).Font.Size = 12 + i
Next i

End Sub

This example clears the formula in cell one on Sheet1.

Sub Sample2()

Worksheets("Sheet1").Cells(1).ClearContents

End Sub

This example sets the font and font size for every cell on Sheet1 to 8-point Arial

Sub Sample3()

With Worksheets("Sheet1").Cells.Font
    .Name = "Arial"
    .Size = 8
End With

End Sub

Download example file

How To Use Range in VBA

Worksheet.range returns a Range object that represents a cell or a range of cells.

Sub Sample1()
    Range("A1").Value = 3.14159
    Range("A2").Value = 3.14159
    Range("A2").Offset(1, 0).Value = 3.14159
End Sub

Sub Sample2()
    Range("A1").Formula = "=10*RAND()"
    Range("A2").Formula = "=10*RAND()"
    Range("A2").Offset(1, 0).Formula = "=10*RAND()"
End Sub

Sub Sample3()
Dim Val As Integer
Val = 0
For Each c In Worksheets("Sheet1").Range("A1:D10")
    If c.Value < 0.001 Then
        c.Value = Val
        Val = Val + 1
    End If
Next c
End Sub

Now, we define range name with use Formulas > Name Manager
Give a name to A1:D10 with "MyRange"

Sub Sample4()
Dim Val As Integer
Val = 0
For Each c In Worksheets("Sheet1").Range("MyRange")
    If c.Value < 0.001 Then
        c.Value = Val
        Val = Val + 1
    End If
Next c
End Sub

Range also can use with Range(Cell1, Cell2)
example A1 = Cell(1,1) , D10 = Cell(10,4)
which Cell(Row, col)

Sub Sample5()
Dim Val As Integer
Val = 0
For Each c In Worksheets("Sheet1").Range(Cells(1, 1), Cells(10, 4))
    If c.Value < 0.001 Then
        c.Value = Val
        Val = Val + 2
    End If
Next c
End Sub

Download example file

How to Merge Cells in Excel VBA

You can't split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it. For example, you want to split cell A2 into three cells that will appear, side-by-side, under cell A1 (you want to utilize cell A1 as a heading). It is not possible to split cell A2, but you can achieve a similar effect by merging cells A1, B1, and C1 into one, single cell. You then enter your data in cells A2, B2, and C2. These three cells appear as if they are split under one larger cell (A1) that acts as a heading.
----------------------------------------------------------------

1
2
3

Sub Sample1()
    Range("A1:B5").Merge
End Sub

If you run above macro, it will become like this

----------------------------------------------------------------
Now, how to check cells merge or not,

Sub Sample2()
    Dim i As Long, buf As String
    For i = 9 To 22
        If Cells(i, 1).MergeCells Then
            buf = buf & Cells(i, 1).Address(0, 0) & "-->Merged Cell" & vbCrLf
        Else
            buf = buf & Cells(i, 1).Address(0, 0) & "-->Not Merge Cell" & vbCrLf
        End If
    Next i
    MsgBox buf
End Sub

----------------------------------------------------------------
Merge Area

Sub Sample3()
    Dim Target As String, i As Long, buf As String, c
    Target = InputBox("Seelct Year Target")
    If Target = "" Then Exit Sub
    For i = 9 To 22
        If Cells(i, 1) = Target Then
            buf = Target & "(" & Cells(i, 1).Address(0, 0) & ")" & vbCrLf
            buf = buf & "----------" & vbCrLf
            For Each c In Cells(i, 1).MergeArea
                buf = buf & c.Address(0, 0) & vbCrLf
            Next c
            MsgBox buf
            Exit For
        End If
    Next i
End Sub

----------------------------------------------------------------

Sub Sample4()
    Dim buf As String
    With Range("A30").MergeArea
        buf = buf & .Rows.Count & " row" & vbCrLf
        buf = buf & .Columns.Count & " coloum" & vbCrLf
        buf = buf & .Count & " cells" & vbCrLf
        buf = buf & .Item(1).Address(0, 0) & " :top left " & vbCrLf
        buf = buf & .Item(.Count).Address(0, 0) & " :bottom right"
    End With
    MsgBox buf
End Sub

----------------------------------------------------------------

Link file download

excelpaidjo

Pages

Saturday, September 19, 2015

How to Use Cell in Excel VBA

How To Use Range in VBA

How to Merge Cells in Excel VBA