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

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


"Gerrym" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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<at>tarkon.ee
>Arvi Laanemets
>
>
>"Gerrym" <[email protected]> wrote in
message
>news:[email protected]...
>> 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
>
>
>.
>

Bob
Your reply didn't come through.

Gerry

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> Bob
> Your reply didn't come through.
>
> Gerry
>
> >-----Original Message-----
> >Message unavailable

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" <[email protected]> wrote in message
news:[email protected]...
> 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<at>tarkon.ee
> >Arvi Laanemets
> >
> >
> >"Gerrym" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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

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
>
>
>"Gerrym" <[email protected]> wrote in
message
>news:[email protected]...
>> 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
>
>
>.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >"Gerrym" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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
> >
> >
> >.
> >

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"
<[email protected]> 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<at>tarkon.ee
>>Arvi Laanemets
>>
>>
>>"Gerrym" <[email protected]> wrote in
>message
>>news:[email protected]...
>>> 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
>>
>>
>>.
>>