Joy E. Daniels     ♦ Programming ♦ Reporting ♦ Support ♦

Description

VBA Code

Prompt user and edit a formula

Sub EditFormula()

Application.ScreenUpdating = False

Dim strVar As String

strVar = InputBox("Enter a date (m/d/yy)", _

"Calculate 30-day date")

Range("A1").FormulaR1C1 =

"=DATEVALUE(""" + strVar + """)+30"

Range("A1").NumberFormat = "m/d/yyyy"

End Sub

Prompt user to edit a cell

Sub EditCell()

Dim strOld As String, strNew As String

strOld = Range("A1").Value

strNew = InputBox("Edit this text:", "CELL EDITING", strOld)

If strNew <> "" Then Range("A1").Value = strNew

End Sub

Show a built-in dialog box

Sub ChangeFont()

Application.Dialogs(xlDialogFormatFont).Show

End Sub

Paste the value of a formula into a cell, not the formula itself

Sub PasteValues()

Range("A4").Copy

Range("D4").PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

End Sub

Move 3 rows down and 2 columns to the right

Sub MoveToCell()

ActiveCell.Offset(3, 2).Select

End Sub

Go to a blank row within a list

Sub GoToEnd()

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

End Sub

Select to the last row and column of a list

Sub SelectToEnd()

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlToRight)).Select

End Sub

Go to the last cell of a spreadsheet

Sub LastCell()

Selection.SpecialCells(xlLastCell).Select

End Sub

Select an entire list

Sub SelectList()

ActiveCell.CurrentRegion.Select

End Sub

Select 2 separate ranges

Sub SelectRanges()

Dim r1 As Range, r2 As Range, rAll As Range

Set r1 = Range("A1", "A3")

Set r2 = Range("C3", "C8")

Set rAll = Union(r1, r2)

rAll.Select

End Sub

Close a file without saving it

Sub CloseNoSave()

ActiveWindow.Close SaveChanges:=False

End Sub

Fill all cells of a selected range with a value

Sub FillNumber()

Dim c As Range

For Each c In Selection

c.Value = 2

Next c

End Sub

Insert filename and path into a cell

Sub FilenameInCell()

ActiveCell.Value = ActiveWorkbook.FullName

End Sub

Insert filename and path into a footer

Sub FilenameInFooter()

ActiveSheet.PageSetup.CenterFooter = _

ActiveWorkbook.FullName

End Sub