Hi,
I execute a macro in a blank sheet. The macro will extract information
from another sheet called aaa.

1) How do I get the number of non-blank rows in sheet aaa into the
variable called mr? Column 1 in sheet aaa can be used .

2) How do I place a value into the current sheet from sheet aaa? I have
coded the following:

cells(1,1).value = cells(aaa!8,5).value

TIA. Pierre.

--
Posted via a free Usenet account from http://www.teranews.com

Pierre,

mr = Application.WorksheetFunction.CountA(Range(Cells(2, 1), Cells(10, 1)))

Cells(1,1).Value = sheets("aaa").Cells(8, 5).Value

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Pierre Fichaud" <(E-Mail Removed)> wrote in message
news:47c873c9$0$26063$(E-Mail Removed)...
> Hi,
> I execute a macro in a blank sheet. The macro will extract information from another sheet
> called aaa.
>
> 1) How do I get the number of non-blank rows in sheet aaa into the variable called mr?
> Column 1 in sheet aaa can be used .
>
> 2) How do I place a value into the current sheet from sheet aaa? I have coded the
> following:
>
> cells(1,1).value = cells(aaa!8,5).value
>
> TIA. Pierre.
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

Pierre,

Oops. I didn't notice the sheet aaa requirement. Make that:

mr = Application.WorksheetFunction.CountA(Range(Sheets("aaa").Cells(2, 1),
Sheets("aaa").Cells(10, 1)))

Or

mr = Application.WorksheetFunction.CountA(Sheets("aaa").Range("A2:A10"))

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Earl Kiosterud" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Pierre,
>
> mr = Application.WorksheetFunction.CountA(Range(Cells(2, 1), Cells(10, 1)))
>
> Cells(1,1).Value = sheets("aaa").Cells(8, 5).Value
>
> --
> Regards from Virginia Beach,
>
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "Pierre Fichaud" <(E-Mail Removed)> wrote in message
> news:47c873c9$0$26063$(E-Mail Removed)...
>> Hi,
>> I execute a macro in a blank sheet. The macro will extract information from another sheet
>> called aaa.
>>
>> 1) How do I get the number of non-blank rows in sheet aaa into the variable called mr?
>> Column 1 in sheet aaa can be used .
>>
>> 2) How do I place a value into the current sheet from sheet aaa? I have coded the
>> following:
>>
>> cells(1,1).value = cells(aaa!8,5).value
>>
>> TIA. Pierre.
>>
>> --
>> Posted via a free Usenet account from http://www.teranews.com
>>
>
>

Here is coding for the first question:

Sub marine()
Sheets("aaa").Activate
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
Non_Blank_Rows = 0
For i = 1 To nLastRow
If Application.CountA(Rows(i)) > 0 Then
Non_Blank_Rows = Non_Blank_Rows + 1
End If
Next
MsgBox (Non_Blank_Rows)
mr = Non_Blank_Rows
End Sub

We cannot simply look at the limits of UsedRange. There may be blank rows
embedded in aaa.

Here is coding for the second question:

Sub servient()
Cells(1, 1).Value = Sheets("aaa").Cells(8, 5).Value
End Sub
--
Gary''s Student - gsnu2007d


"Pierre Fichaud" wrote:

> Hi,
> I execute a macro in a blank sheet. The macro will extract information
> from another sheet called aaa.
>
> 1) How do I get the number of non-blank rows in sheet aaa into the
> variable called mr? Column 1 in sheet aaa can be used .
>
> 2) How do I place a value into the current sheet from sheet aaa? I have
> coded the following:
>
> cells(1,1).value = cells(aaa!8,5).value
>
> TIA. Pierre.
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>
>

Thanks. PF.

Earl Kiosterud wrote:
> Pierre,
>
> mr = Application.WorksheetFunction.CountA(Range(Cells(2, 1), Cells(10, 1)))
>
> Cells(1,1).Value = sheets("aaa").Cells(8, 5).Value
>

--
Posted via a free Usenet account from http://www.teranews.com

I bet you'd need to qualify the Range(), too.

And that's way too much typing:

with worksheets("aaa")
mr = Application.WorksheetFunction.CountA(.Range(.Cells(2, 1), .Cells(10, 1)))
End with

or just:
mr = Application.WorksheetFunction.CountA(Sheets("aaa").range("a2:A10"))

Earl Kiosterud wrote:
>
> Pierre,
>
> Oops. I didn't notice the sheet aaa requirement. Make that:
>
> mr = Application.WorksheetFunction.CountA(Range(Sheets("aaa").Cells(2, 1),
> Sheets("aaa").Cells(10, 1)))
>
> Or
>
> mr = Application.WorksheetFunction.CountA(Sheets("aaa").Range("A2:A10"))
>
> --
> Regards from Virginia Beach,
>
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "Earl Kiosterud" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Pierre,
> >
> > mr = Application.WorksheetFunction.CountA(Range(Cells(2, 1), Cells(10, 1)))
> >
> > Cells(1,1).Value = sheets("aaa").Cells(8, 5).Value
> >
> > --
> > Regards from Virginia Beach,
> >
> > Earl Kiosterud
> > www.smokeylake.com
> > -----------------------------------------------------------------------
> > "Pierre Fichaud" <(E-Mail Removed)> wrote in message
> > news:47c873c9$0$26063$(E-Mail Removed)...
> >> Hi,
> >> I execute a macro in a blank sheet. The macro will extract information from another sheet
> >> called aaa.
> >>
> >> 1) How do I get the number of non-blank rows in sheet aaa into the variable called mr?
> >> Column 1 in sheet aaa can be used .
> >>
> >> 2) How do I place a value into the current sheet from sheet aaa? I have coded the
> >> following:
> >>
> >> cells(1,1).value = cells(aaa!8,5).value
> >>
> >> TIA. Pierre.
> >>
> >> --
> >> Posted via a free Usenet account from http://www.teranews.com
> >>
> >
> >

--

Dave Peterson