The following statement runs in a macro. Runs OK if there is any data in
column V, crashes with Type Mismatch error if column V is blank. How do
I deal with that?

TIA,

Rob

Range("z5").Formula = "=ROW(OFFSET(v1,COUNTA(V:V)-1,0))"


--
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=387127

try
Range("z5").Formula =
"=if(iserror(ROW(OFFSET(v1,COUNTA(V:V)-1,0)),~"~",ROW(OFFSET(v1,COUNTA(V:V)-1,0)"
I think you need the tildes in front of the " if it doesn't work reference a
cell with nothing in it. in this case it cound be
Range("z5").Formula =
"=if(iserror(ROW(OFFSET(v1,COUNTA(V:V)-1,0)),V1,ROW(OFFSET(v1,COUNTA(V:V)-1,0)"
"rroach" wrote:


The following statement runs in a macro. Runs OK if there is any data in
column V, crashes with Type Mismatch error if column V is blank. How do
I deal with that?

TIA,

Rob

Range("z5").Formula = "=ROW(OFFSET(v1,COUNTA(V:V)-1,0))"


--
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=387127

The problem is that with Column V empty, the formula converts to

=IF(ROW(OFFSET(v1,-1,0))) and you can't offset -1 row from Row 1, the
row of Cell V1.

Consider:

Range("z5").Formula = _
"=IF(ISERROR(ROW(OFFSET(V1,COUNTA(V:V)-1,0))),""error"", _
ROW(OFFSET(V1,COUNTA(V:V)-1,0)))"

Alan Beban

rroach wrote:
The following statement runs in a macro. Runs OK if there is any data in
column V, crashes with Type Mismatch error if column V is blank. How do
I deal with that?

TIA,

Rob

Range("z5").Formula = "=ROW(OFFSET(v1,COUNTA(V:V)-1,0))"