Basic VB questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pierre Fichaud - 29 Feb 2008 21:52 GMT

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.

Signature

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

Reply to this Message

Earl Kiosterud - 29 Feb 2008 22:23 GMT

Pierre,

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

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

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Hi,
> I execute a macro in a blank sheet. The macro will extract information from another sheet
[quoted text clipped - 9 lines]
>
> TIA. Pierre.

Reply to this Message

Earl Kiosterud - 29 Feb 2008 22:30 GMT

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

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Pierre,
>
[quoted text clipped - 15 lines]
>>
>> TIA. Pierre.

Reply to this Message

Dave Peterson - 01 Mar 2008 04:22 GMT

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

> Pierre,
>
[quoted text clipped - 41 lines]
> >> --
> >> Posted via a free Usenet account from http://www.teranews.com

Signature

Dave Peterson

Reply to this Message

Pierre Fichaud - 29 Feb 2008 22:44 GMT

Thanks. PF.

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

Signature

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

Reply to this Message

Gary''s Student - 29 Feb 2008 22:35 GMT

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

Signature

Gary''s Student - gsnu2007d

> Hi,
> I execute a macro in a blank sheet. The macro will extract information
[quoted text clipped - 9 lines]
>
> TIA. Pierre.

Reply to this Message

Rate this thread: