Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple CountA syntax (VBA)
I have a worksheet where users are entering data from D1 to IV1 (headers); I
need to count how many entries exist so I can automatically paste entries at the next unused cell (all contiguous cells will be filled, There won't be any blanks). I tried the following, but the range reference for CountA doesn't appear to translate well (clearly, I'm not getting the proper syntax). Can anyone correct it, or provide an easy alternative in VBA? Thanks, Keith XL2003 not working: ----------------- OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1")) and OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1").value) Each returns the value of 1; in a cell on that worksheet, =countA(D1:IV1) evaluates to 34 (and will be growing quickly) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple CountA syntax (VBA)
Demo'd from the immediate window:
OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1")) ? oldapps 6 works fine for me. Sheet3 refers to a sheet with the code name of Sheet3. I would guess that you want the sheet with a tab name of sheet3. (while these usually match in a new workbook, they certainly don't have to). Try this Dim rng as range set rng = Worksheets("Sheet3").Range("D1:IV1") OldApps = Excel.WorksheetFunction.CountA(rng) another way to know where to enter the value Dim rng1 as Range set rng1 = worksheets("Sheet3").Range("IV1").End(xltoLeft) if rng1.column 3 then rng1.offset(0,1).Value = "NewHeader" else worksheets("Sheet3").Range("D1").Value = "NewHeader" End if -- Regards, Tom Ogilvy "Keith" wrote: I have a worksheet where users are entering data from D1 to IV1 (headers); I need to count how many entries exist so I can automatically paste entries at the next unused cell (all contiguous cells will be filled, There won't be any blanks). I tried the following, but the range reference for CountA doesn't appear to translate well (clearly, I'm not getting the proper syntax). Can anyone correct it, or provide an easy alternative in VBA? Thanks, Keith XL2003 not working: ----------------- OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1")) and OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1").value) Each returns the value of 1; in a cell on that worksheet, =countA(D1:IV1) evaluates to 34 (and will be growing quickly) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple CountA syntax (VBA)
Thanks Tom- I'm going to use the xltoLeft, I like that solution!
"Tom Ogilvy" wrote in message ... Demo'd from the immediate window: OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1")) ? oldapps 6 works fine for me. Sheet3 refers to a sheet with the code name of Sheet3. I would guess that you want the sheet with a tab name of sheet3. (while these usually match in a new workbook, they certainly don't have to). Try this Dim rng as range set rng = Worksheets("Sheet3").Range("D1:IV1") OldApps = Excel.WorksheetFunction.CountA(rng) another way to know where to enter the value Dim rng1 as Range set rng1 = worksheets("Sheet3").Range("IV1").End(xltoLeft) if rng1.column 3 then rng1.offset(0,1).Value = "NewHeader" else worksheets("Sheet3").Range("D1").Value = "NewHeader" End if -- Regards, Tom Ogilvy "Keith" wrote: I have a worksheet where users are entering data from D1 to IV1 (headers); I need to count how many entries exist so I can automatically paste entries at the next unused cell (all contiguous cells will be filled, There won't be any blanks). I tried the following, but the range reference for CountA doesn't appear to translate well (clearly, I'm not getting the proper syntax). Can anyone correct it, or provide an easy alternative in VBA? Thanks, Keith XL2003 not working: ----------------- OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1")) and OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV 1").value) Each returns the value of 1; in a cell on that worksheet, =countA(D1:IV1) evaluates to 34 (and will be growing quickly) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTA function causing syntax error in macro... | Excel Worksheet Functions | |||
question about syntax in a simple macro... | Excel Discussion (Misc queries) | |||
COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Syntax error (simple) | Excel Programming | |||
need help with simple syntax error | Excel Programming |