Loop?

[Varoujan]

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

 

[Dave Miller]

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

next i

 

[Varoujan]

Thank you Dave ,

Varouj

 

[Don Guillett]

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

 

[Tim]

Try

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

Tim

 

[Varoujan]

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

 

[Gary Keramidas]

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

 

[Varoujan]

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]

yes


--


Gary

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

 

[Don Guillett]

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")

 

[Varoujan]

Hi,

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

Varouj

 

[Don Guillett]

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

 

[sharad.net]

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