Hi,

Thanks Don for the comprehensiv explanation on use of Excel functions.
My small program works now, thank you all !

Varouj

"Don Guillett" a écrit dans le message de news:
...
From Help
Using Microsoft Excel Worksheet Functions in Visual Basic
See AlsoSpecifics
You can use most Microsoft Excel worksheet functions in your Visual Basic
statements. To see a list of the worksheet functions you can use, see List
of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions aren’t useful in Visual Basic. For example,
the Concatenate function isn’t needed because in Visual Basic you can use
the & operator to join multiple text values.

Calling a Worksheet Function from Visual Basic
In Visual Basic, the Microsoft Excel worksheet functions are available
through the WorksheetFunction object.

The following Sub procedure uses the Min worksheet function to determine
the smallest value in a range of cells. First, the variable myRange is
declared as a Range object, and then it’s set to range A1:C10 on Sheet1.
Another variable, answer, is assigned the result of applying the Min
function to myRange. Finally, the value of answer is displayed in a
message box.

Sub UseFunction()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer
End Sub
If you use a worksheet function that requires a range reference as an
argument, you must specify a Range object. For example, you can use the
Match worksheet function to search a range of cells. In a worksheet cell,
you would enter a formula such as =MATCH(9,A1:A10,0). However, in a Visual
Basic procedure, you would specify a Range object to get the same result.

Sub FindFirst()
myVar = Application.WorksheetFunction _
.Match(9, Worksheets(1).Range("A1:A10"), 0)
MsgBox myVar
End Sub
Note Visual Basic functions don’t use the WorksheetFunction qualifier. A
function may have the same name as a Microsoft Excel function and yet work
differently. For example, Application.WorksheetFunction.Log and Log will
return different values.

Inserting a Worksheet Function into a Cell
To insert a worksheet function into a cell, you specify the function as
the value of the Formula property of the corresponding Range object. In
the following example, the RAND worksheet function (which generates a
random number) is assigned to the Formula property of range A1:B3 on
Sheet1 in the active workbook.

Sub InsertFormula()
Worksheets("Sheet1").Range("A1:B3").Formula = "=RAND()"
End Sub
Example
This example uses the worksheet function Pmt to calculate a home mortgage
loan payment. Notice that this example uses the InputBox method instead of
the InputBox function so that the method can perform type checking. The
Static statements cause Visual Basic to retain the values of the three
variables; these are displayed as default values the next time you run the
program.

Static loanAmt
Static loanInt
Static loanTerm
loanAmt = Application.InputBox _
(Prompt:="Loan amount (100,000 for example)", _
Default:=loanAmt, Type:=1)
loanInt = Application.InputBox _
(Prompt:="Annual interest rate (8.75 for example)", _
Default:=loanInt, Type:=1)
loanTerm = Application.InputBox _
(Prompt:="Term in years (30 for example)", _
Default:=loanTerm, Type:=1)
payment = Application.WorksheetFunction _
.Pmt(loanInt / 1200, loanTerm * 12, loanAmt)
MsgBox "Monthly payment is " & Format(payment, "Currency")

--
Don Guillett
SalesAid Software

"Varoujan" wrote in message
...
Thanks to all !

The one that works for me is : Application.CountA(Range("A2:A50"))

Funny, but when used directly in the worksheet, the "range" word is not
necessary.
It seems compulsory in VBA !

Another question please;

In the command : Worksheet("Name of sheet").activate
Can "Name of sheet" be a variable ? an array variable ? must be defined
as "string" of course ?

Varouj




"Tim" <tim j williams at gmail dot com a écrit dans le message de news:
...
Try

Application.CountA()
or
Application.Worksheetfunction.CountA()

Tim



"Varoujan" wrote in message
...
Hi ,

I use excel but I'm new to programming, please excuse me if this is
trivial.
I have information on one sheet : let's call it Mainsheet and I want to
copy that info on several other shhets in the following manner.
The information on Main is like a database with the rows being the
records.
In the first cell of the row is one " name of the company".
I want to copy the information of that record on a sheet named the same
name as the company name and the info transposed in a column "B". the
top record labels being in column A of the company sheet. (I hope this
makes sense).
When I record a macro, everything goes well of course but Ias soon as I
try to make a loop to go through every record, I have errors.
Can someone please give me an idea of how this should be done ?
I already have created the company sheets, so no need to include the
creation of new sheets.

One additional question :
It seems that the "counta" function does not behave the same way when
in vba !
I get an error message whichever way I use it ?

Can I write :
For i= 1 to counta("A2:A50")
................do this , do that
next i

TIA