| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
No Message
Bob Your reply didn't come through. Gerry -----Original Message----- Message unavailable |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using COUNTA for a variable array | Excel Worksheet Functions | |||
| Setting a Date variable | Excel Programming | |||
| Setting variable = to sheet name | Excel Programming | |||
| setting a range variable equal to the value of a string variable | Excel Programming | |||
| Setting range value to a variable | Excel Programming | |||
Linear Mode
