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

For i = 1 to Range("A65536").end(xlup).row

next i

Thank you Dave ,

Varouj

"Dave Miller" a écrit dans le message de news:
...
For i = 1 to Range("A65536").end(xlup).row

next i

try this to see which you REALLY want to use. Counta only counts if
something there.

Sub usecounta()
'For i = 1 To CountA("A2:A50")
'................do this , do that
' Next i
MsgBox Application.CountA(Range("a2:a50"))
MsgBox Range("a2:a50").Rows.Count

End Sub

--
Don Guillett
SalesAid Software

"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

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

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

some examples:

you can use the index number:
Worksheets(2).Activate

you can also use a variable:
dim i as long
for i = 1 to 3
Worksheets(i).Activate
next

or for each:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Next
--


Gary


"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

Thank you Gary for your answer.
But if I want to go to a specific worksheet, can I use a string variable in
the command
Worksheet("name").activate

TIA
VArouj


"Gary Keramidas" <GKeramidasATmsn.com a écrit dans le message de news:
...
some examples:

you can use the index number:
Worksheets(2).Activate

you can also use a variable:
dim i as long
for i = 1 to 3
Worksheets(i).Activate
next

or for each:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Next
--


Gary


"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

yes


--


Gary


"Varoujan" wrote in message
...
Thank you Gary for your answer.
But if I want to go to a specific worksheet, can I use a string variable in
the command
Worksheet("name").activate

TIA
VArouj


"Gary Keramidas" <GKeramidasATmsn.com a écrit dans le message de news:
...
some examples:

you can use the index number:
Worksheets(2).Activate

you can also use a variable:
dim i as long
for i = 1 to 3
Worksheets(i).Activate
next

or for each:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Next
--


Gary


"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

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

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

Wasn't my explanation. It was right there in the help files.

--
Don Guillett
SalesAid Software

"Varoujan" wrote in message
...
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

Hi,

I did a similar macro to copy records from main sheet, to various
other sheets, following a criteria. And also did the sub total by
itself. If you face any other problem, do let me know.

Sharad