Basic VB questions

[Pierre Fichaud]

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.

 

[Earl Kiosterud]

Pierre,

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

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

 

[Earl Kiosterud]

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

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

 

[Gary''s Student]

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

 

[Pierre Fichaud]

Thanks. PF.

Pierre,

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

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

 

[Dave Peterson]

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,

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