Setting a variable using COUNTA

  • Thread starter Thread starter Gerrym
  • Start date Start date
G

Gerrym

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
 
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]
....
 
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)
 
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)"
Click to expand...
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<at>tarkon.ee
Arvi Laanemets


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
Click to expand...


.
Click to expand...
 
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)
 
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<at>tarkon.ee
Arvi Laanemets



Gerrym said:
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)"
Click to expand...
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<at>tarkon.ee
Arvi Laanemets


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
Click to expand...


.
Click to expand...
Click to expand...
 
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<at>tarkon.ee
Arvi Laanemets


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
Click to expand...


.
Click to expand...
 
Gerry,

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

--

HTH

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


Gerrym said:
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<at>tarkon.ee
Arvi Laanemets


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
Click to expand...


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

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


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<at>tarkon.ee
Arvi Laanemets


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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top