Setting a variable using COUNTA
 

Can I set a variable using COUNTA to count non blank rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS

Setting a variable using COUNTA
 

Hi

Define the number of rows as named value, i.e
RowNum=COUNTA(Sheet1!$A:$A)

In VBA, you can always refer to named value:
....
varNumberOfRows=[RowNum]
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Gerrym" wrote in message
...
Can I set a variable using COUNTA to count non blank rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS

Setting a variable using COUNTA
 

You can count the number of non-blank cells in a range like so

myVar = Application.COUNTA(Range("A1:B10")

but not the number of blank rows, because if two cells on the same row have
data, you will get 2 not 1.

To get that, you would need to check each row, like so

Dim oRow As Range
Dim cNonBlanks As Long

For Each oRow In Range("50:80").Rows
If Application.CountA(oRow) < 0 Then
cNonBlanks = cNonBlanks + 1
End If
Next oRow

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gerrym" wrote in message
...
Can I set a variable using COUNTA to count non blank rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS

Setting a variable using COUNTA
 

Arvi

Can you show me how to use it in the syntax of my VLookup
i.e
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"
Tks

-----Original Message-----
Hi

Define the number of rows as named value, i.e
RowNum=COUNTA(Sheet1!$A:$A)

In VBA, you can always refer to named value:
....
varNumberOfRows=[RowNum]
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Gerrym" wrote in
message
...
Can I set a variable using COUNTA to count non blank
rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS


.

No Message
 

Bob
Your reply didn't come through.

Gerry

-----Original Message-----
Message unavailable

No Message
 

Try again

You can count the number of non-blank cells in a range like so

myVar = Application.COUNTA(Range("A1:B10")

but not the number of blank rows, because if two cells on the same row have
data, you will get 2 not 1.

To get that, you would need to check each row, like so

Dim oRow As Range
Dim cNonBlanks As Long

For Each oRow In Range("50:80").Rows
If Application.CountA(oRow) < 0 Then
cNonBlanks = cNonBlanks + 1
End If
Next oRow



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gerrym" wrote in message
...

Bob
Your reply didn't come through.

Gerry

-----Original Message-----
Message unavailable

Setting a variable using COUNTA
 

Hi

variable=[YourNamedValue]
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!R2C1:R" & variable+1 &
"C2,2)"
or
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!R2C1:R" & [YourNamedValue]+1
& "C2,2)"

where YourNamedValue contains the number of rows in table (minus header row)

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Gerrym" wrote in message
...
Arvi

Can you show me how to use it in the syntax of my VLookup
i.e
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"
Tks

-----Original Message-----
Hi

Define the number of rows as named value, i.e
RowNum=COUNTA(Sheet1!$A:$A)

In VBA, you can always refer to named value:
....
varNumberOfRows=[RowNum]
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Gerrym" wrote in
message
...
Can I set a variable using COUNTA to count non blank
rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS


.

Setting a variable using COUNTA
 

Hi

I get an error when I use RowNum=COUNTA(Sheet1!$A:$A)
"Invalid Character , Compile error", Does not like $.
When I remove the $ I get an error "Expected : List or
seperator )"

Tks again

-----Original Message-----
Hi

Define the number of rows as named value, i.e
RowNum=COUNTA(Sheet1!$A:$A)

In VBA, you can always refer to named value:
....
varNumberOfRows=[RowNum]
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Gerrym" wrote in
message
...
Can I set a variable using COUNTA to count non blank
rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS


.

Setting a variable using COUNTA
 

Gerry,

Arvi means create an Excel workbook name (InsertNameDefine Name) to create
it, not VBA.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gerrym" wrote in message
...
Hi

I get an error when I use RowNum=COUNTA(Sheet1!$A:$A)
"Invalid Character , Compile error", Does not like $.
When I remove the $ I get an error "Expected : List or
seperator )"

Tks again

-----Original Message-----
Hi

Define the number of rows as named value, i.e
RowNum=COUNTA(Sheet1!$A:$A)

In VBA, you can always refer to named value:
....
varNumberOfRows=[RowNum]
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Gerrym" wrote in
message
...
Can I set a variable using COUNTA to count non blank
rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS


.

Setting a variable using COUNTA
 

VBA doesn't understand formulas when written just like you write them on a
worksheet.

RowNum = Application.COUNTA(Worksheets("Sheet1").Columns(1) )


On Thu, 24 Mar 2005 04:40:40 -0800, "Gerrym"
wrote:

Hi

I get an error when I use RowNum=COUNTA(Sheet1!$A:$A)
"Invalid Character , Compile error", Does not like $.
When I remove the $ I get an error "Expected : List or
seperator )"

Tks again

-----Original Message-----
Hi

Define the number of rows as named value, i.e
RowNum=COUNTA(Sheet1!$A:$A)

In VBA, you can always refer to named value:
....
varNumberOfRows=[RowNum]
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Gerrym" wrote in
message
...
Can I set a variable using COUNTA to count non blank
rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS


.