VBA/Excel/Access/Word/Excel/Cell Value

Assing value in Cells to array, do the bubble sort and assign them back

 
Public Sub BubbleSort2()
    Dim tempVar As Integer
    Dim anotherIteration As Boolean
    Dim I As Integer
    Dim myArray(10) As Integer
    For I = 1 To 10
        myArray(I - 1) = Cells(I, "A").Value
    Next I
    Do
        anotherIteration = False
        For I = 0 To 8
            If myArray(I) > myArray(I + 1) Then
                tempVar = myArray(I)
                myArray(I) = myArray(I + 1)
                myArray(I + 1) = tempVar
                anotherIteration = True
            End If
        Next I
    Loop While anotherIteration = True
    For I = 1 To 10
        Cells(I, "B").Value = myArray(I - 1)
    Next I
End Sub

Calculate cell

 
Sub CalcCell()
      Worksheets("Sheet1").range("A1").Calculate
End Sub

Checks values in a range 10 rows by 5 columns

 
Sub CheckValues1()
    Dim rwIndex As Integer
    Dim colIndex As Integer
    For rwIndex = 1 To 10
            For colIndex = 1 To 5
                If Cells(rwIndex, colIndex).Value <> 0 Then _
                    Cells(rwIndex, colIndex).Value = 0
            Next colIndex
    Next rwIndex
End Sub

Checks values in a range 10 rows by 5 columns with nested for loop

 
Sub CheckValues2()
    Dim rwIndex As Integer
    Dim colIndex As Integer
    For rwIndex = 1 To 10
         For colIndex = 1 To 5
             With Cells(rwIndex, colIndex)
                 If Not (.Value = 0) Then Cells(rwIndex, colIndex).Value = 0
             End With
         Next colIndex
    Next rwIndex
End Sub

Define a string type variable and set to Cell(1,D)

 
Sub Strtype()
    Dim myHeading As String
    myHeading = "asdf"
    Cells(1, "D").Value = myHeading
End Sub

Determining a cell"s data type

 
Function CellType(Rng)
    Application.Volatile
    Set Rng = Rng.Range("A1")
    Select Case True
        Case IsEmpty(Rng)
            CellType = "Blank"
        Case WorksheetFunction.IsText(Rng)
            CellType = "Text"
        Case WorksheetFunction.IsLogical(Rng)
            CellType = "Logical"
        Case WorksheetFunction.IsErr(Rng)
            CellType = "Error"
        Case IsDate(Rng)
            CellType = "Date"
        Case InStr(1, Rng.Text, ":") <> 0
            CellType = "Time"
        Case IsNumeric(Rng)
            CellType = "Value"
    End Select
End Function

Format("ALL LOWERCASE ", "

 
Sub callLower()
    Cells(2, "A").Value = Format("ALL LOWERCASE ", "<")
End Sub

Place the value (result) of a formula into a cell rather than the formula.

 
Sub GetSum()                    " using the shortcut approach
    [A1].Value = Application.Sum([E1:E15])
End Sub

StrConv("ALL LOWERCASE ", vbLowerCase)

 
Sub STRConvDemo()
    Cells(3, "A").Value = STRConv("ALL LOWERCASE ", vbLowerCase)
End Sub

transposes the values of a group of cells in a worksheet

 
Public Sub Transpose()
    Dim I As Integer
    Dim J As Integer
    Dim transArray(9, 2) As Integer
    For I = 1 To 3
        For J = 1 To 10
            transArray(J - 1, I - 1) = Cells(J, Chr(I + 64)).Value
        Next J
    Next I
    Range("A1:C10").ClearContents
    For I = 1 To 3
        For J = 1 To 10
            Cells(I, Chr(J + 64)).Value = transArray(J - 1, I - 1)
        Next J
    Next I
End Sub

Validation with a Spreadsheet Cell

 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellContents As String
    Dim valLength As Integer
    cellContents = Trim(Str(Val(Target.Value)))
    valLength = Len(cellContents)
    If valLength <> 3 Then
        MsgBox ("Please enter a 3 digit area code.")
        Cells(9, "C").Select
    Else
        Cells(9, "C").Value = cellContents
        Cells(9, "D").Select
    End If
End Sub