type mismatch--how to fix

[rroach]

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))"

 

[Guest]

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)"

 

[Alan Beban]

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