Showing posts from February, 2013

MS Excel Error Messages

Excel Error Messages 
Excel lets you know about it with an error message If you create a formula in Excel. A handful of errors can appear in a cell when a formula or function in Excel cannot be resolved. Knowing their meaning helps correct the problem. ErrorMeaning#DIV/0!Trying to divide by 0#N/A!A formula or a function inside a formula cannot find the referenced data#NAME?Text in the formula is not recognized#NULL!A space was used in formulas that reference multiple ranges; a comma separates range references#NUM!A formula has invalid numeric data for the type of operation#REF!A reference is invalid#VALUE!The wrong type of operand or function argument is used

Common Visual Basic for Application (VBA) Statements

AppActivateActivates an application windowBeepSounds a tone via the computer's speakerCallTransfers control to another procedureChDirChanges the current directoryChDriveChanges the current driveCloseCloses a text fileConstDeclares a constant valueDateSets the current system dateDeclareDeclares a reference to an external procedure in a Dynamic Link Library (DLL)DeleteSettingDeletes a section or key setting from an application's entry in the Windows RegistryDimDeclares variables and (optionally) their data typesDo-LoopLoops through a set of instructionsEndUsed by itself, exits the program; also used to end a block of statements that begin with If, With, Sub, Function, Property, Type, or SelectEraseRe-initializes an arrayErrorSimulates a specific error conditionExit DoExits a block of Do-Loop codeExit ForExits a block of For-Next codeExit FunctionExits a Function procedureExit PropertyExits a property procedureExit SubExits a subroutine procedureFileCopyCopies a fileFor Each-Next

How To Use COUNTIF in Visual Basic Application

In Excel, the COUNTIF function is used to count the number of cells in a selected range that meets your requirement in the spreadsheet.

The COUNTIF function is typically written in Excel as:

=COUNTIF ( Range, Criteria)

where "Range" = the group of cells the function is to search.

However, in Visual Basic Application, the COUNTIF function is used as below:

Dim I As Integer
For I = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet1.Cells(I, 14) = WorksheetFunction.CountIf(Sheet1.Range("M1:M" & I), Sheet1.Cells(I, 13))
Next I
End Sub

Order ReasonOrder ReasonRunning Repair1Promo Service1Promo Service2Paid Service1Running Repair2Paid Service2Promo Service3Body Repair1Running Repair3Running Repair4