Excel And VBA Tips

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. Error Meaning #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

AppActivate Activates an application window Beep Sounds a tone via the computer's speaker Call Transfers control to another procedure ChDir Changes the current directory ChDrive Changes the current drive Close Closes a text file Const Declares a constant value Date Sets the current system date Declare Declares a reference to an external procedure in a Dynamic Link Library (DLL) DeleteSetting Deletes a section or key setting from an application's entry in the Windows Registry Dim Declares variables and (optionally) their data types Do-Loop Loops through a set of instructions End Used by itself, exits the program; also used to end a block of statements that begin with If, With, Sub, Function, Property, Type, or Select Erase Re-initializes an array Error Simulates a specific error condition Exit Do Exits a block of Do-Loop code Exit For Exits a block of For-Next code Exit Function Exits a Function procedure Exit Property Exits a property procedure Exit Sub Exi

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: Sub COUNTIF_FIRSTTOLAST() 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 Reason Order Reason Running Repair 1 Promo Service 1 Promo Service 2 Paid Service 1 Running Repair 2 Paid Service 2 Promo Service 3 Body Repair 1 Running Repair 3 Running Repair 4