Simple CountA syntax (VBA)

[Keith]

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:IV1"))
and
OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV1").value)

Each returns the value of 1; in a cell on that worksheet, =countA(D1:IV1)
evaluates to 34 (and will be growing quickly)

 

[Guest]

Demo'd from the immediate window:

OldApps = Excel.WorksheetFunction.CountA(Sheet3.Range("D1:IV1"))
? 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

 

[Keith]

Thanks Tom- I'm going to use the xltoLeft, I like that solution!