Excel Macro for Beginners: Hitung Red Font

Thursday, November 20, 2008

Hitung Red Font

---------- Forwarded message ----------
From: Safri Ishak <safri.ishak@gmail.com>
Date: Wed, Nov 12, 2008 at 6:30 PM
Subject: Re: ]] XL-mania [[ Hitung red Font
To: XL-mania@yahoogroups.com

Dear John,

if you dare :) to use macro, please download and try the attached excel file.

siapa takut .... he he.

--
Thank you and regards,
Safri
www.tb512.com my virtual home
www.tebetbarat.com Tebet Business Directory consists of addresses and phone numbers of favorite restaurants, traditional markets, hotels, offices, schools, super markets, malls, automotive, gardens, flowers, cakes, advertising, computers, salons, barber shops, cosmetics, banks, apartments etc.
Originally it was compiled for personal purposes and then published to the internet as a gateway to search business directory and websites in Tebet and surrounding area.

2008/11/12 John Robert May <jrobertm@abadiadimulia.com>

Dear XL Mania,

Minta tolon seperti kasus dibawah ini,

Jakarta

23

Semarang

16

Surabaya

22

Bali

11

Makasar

19

Jumlah Angka "Red Font"

2

( Note : didapat dari Conditional Formating)

Tanya : Bagaimana rumusnya pada Cell D10, supaya dapat menghitung jumlah angka yg ber - Red Font pada Range D2 - D6

Help me please

--
Thank you and regards,
Safri
www.tb512.com my virtual home
www.tebetbarat.com Tebet Business Directory consists of addresses and phone numbers of favorite restaurants, traditional markets, hotels, offices, schools, super markets, malls, automotive, gardens, flowers, cakes, advertising, computers, salons, barber shops, cosmetics, banks, apartments etc.
Originally it was compiled for personal purposes and then published to the internet as a gateway to search business directory and websites in Tebet and surrounding area.

No comments:

Post a Comment

macro statements

Exctracted from Excel Macro Help.

Option Explicit Statement
Used at module level to force explicit declaration of all variables in that module.
Syntax
Option Explicit
Remarks
If used, the Option Explicit statement must appear in a module before any procedures.
When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.
If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.
Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.

Option Explicit Statement
Dengan menggunakan option ini, maka semua variable (akan kita diskusikan lebih lanjut) harus di deklarasikan.
Option Explicit harus dibuat dan diletakkan paling atas, sebelum prosedur yang lain. Untuk menghindari kesalahan meng-edit pada waktu menggunakan variable, sangat dianjurkan untuk memakai Option Explicit.

Dim Statement
Declares variables and allocates storage space.
Syntax
Dim [WithEvents] varname[([subscripts])] [As [New] type] [, [WithEvents] varname[([subscripts])] [As [New] type]] . . .The Dim statement syntax has these parts:

Part Description
WithEvents Optional. Keyword that specifies that varname is an object variable used to respond to events triggered by an ActiveX object. WithEvents is valid only in class modules. You can declare as many individual variables as you like using WithEvents, but you can't create arrays with WithEvents. You can't use New with WithEvents.
varname Required. Name of the variable; follows standard variable naming conventions.
subscripts Optional. Dimensions of an array variable; up to 60 multiple dimensions may be declared. The subscripts argument uses the following syntax:
[lower To] upper [, [lower To] upper] . . .

When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.

New Optional. Keyword that enables implicit creation of an object. If you use New when declaring the object variable, a new instance of the object is created on first reference to it, so you don't have to use the Set statement to assign the object reference. The New keyword can't be used to declare variables of any intrinsic data type, can't be used to declare instances of dependent objects, and can’t be used with WithEvents.
type Optional. Data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, a user-defined type, or an object type. Use a separate As type clause for each variable you declare.

Remarks

Variables declared with Dim at the module level are available to all procedures within the module. At the procedure level, variables are available only within the procedure.

Use the Dim statement at module or procedure level to declare the data type of a variable. For example, the following statement declares a variable as an Integer.

Dim NumberOfEmployees As Integer

Also use a Dim statement to declare the object type of a variable. The following declares a variable for a new instance of a worksheet.

Dim X As New Worksheet

If the New keyword is not used when declaring an object variable, the variable that refers to the object must be assigned an existing object using the Set statement before it can be used. Until it is assigned an object, the declared object variable has the special value Nothing, which indicates that it doesn't refer to any particular instance of an object.

You can also use the Dim statement with empty parentheses to declare a dynamic array. After declaring a dynamic array, use the ReDim statement within a procedure to define the number of dimensions and elements in the array. If you try to redeclare a dimension for an array variable whose size was explicitly specified in a Private, Public, or Dim statement, an error occurs.

If you don't specify a data type or object type, and there is no Deftype statement in the module, the variable is Variant by default.

When variables are initialized, a numeric variable is initialized to 0, a variable-length string is initialized to a zero-length string (""), and a fixed-length string is filled with zeros. Variant variables are initialized to Empty. Each element of a user-defined type variable is initialized as if it were a separate variable.

Note When you use the Dim statement in a procedure, you generally put the Dim statement at the beginning of the procedure.

Sub TEST() Statement
Declares the name, arguments, and code that form the body of a Sub procedure.
Syntax

[Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]

End Sub

Sub TEST() Statement
Digunakan untuk mendeklarasikan nama prosedur, argumen dan perintah yang akan membentuk sebuah Sub prosedur. Semua prosedur didalam Excel Macro dimulai dengan Sub statement dan diakhiri dengan End Sub. Dalam contoh ini nama yang digunakan adalah TEST.

LTrim, RTrim, and Trim Functions
Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).
Syntax
LTrim(string)
RTrim(string)
Trim(string)

The required string argument is any valid string expression. If string contains Null, Null is returned.

Replace Function
Description
Returns a string in which a specified substring has been replaced with another substring a specified number of times.
Syntax
Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing substring to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted, the default value is –1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption –1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.

Return Values

Replace returns the following values:

If Replace returns
expression is zero-length Zero-length string ("")
expression is Null An error.
find is zero-length Copy of expression.
replace is zero-length Copy of expression with all occurences of find removed.
start > Len(expression) Zero-length string.
count is 0 Copy of expression.

Tebet Business Directory Alamat Usaha Kita

Free Posting IKLAN GRATIS, send your name, address, telephone, email id, website and brief description of your business to AdminTebetbarat.com

More information about www.TB512.com Click HERE.

Excel Macro for Beginners

TB512 Excel VBA

adsense

adsense links

Thursday, November 20, 2008

Hitung Red Font

No comments:

Please Talk To Me

Labels

Followers

About Me

adsense

macro statements

	Jakarta	23
	Semarang	16
	Surabaya	22
	Bali	11
	Makasar	19



	Jumlah Angka "Red Font"	2	( Note : didapat dari Conditional Formating)

Tanya : Bagaimana rumusnya pada Cell D10, supaya dapat menghitung jumlah angka yg ber - Red Font pada Range D2 - D6

Help me please