excel vba - Copy a Range using a function and report errors -

excel vba - Copy a Range using a function and report errors -

here code

function copytonewrange(a range, b range) ' input, b output if a.cells.count <> b.cells.count copytonewrange = "error" exit function end if = 1 a.cells.count b.cells(i, 1) = a.cells(i, 1) next copytonewrange = "copied" end function

i utilize thus:

=copytonewrange(a11:a30,c11:c30)

in cell not in input or output range! why #value!? note commenting out b.cells(i, 1) = a.cells(i, 1) allows run, error in line?

a udf (a user defined function, called worksheet) cannot directly modify other cells, can homecoming value. there are, however, workarounds.

one such workaround build phone call sub string, , utilize evaluate execute it.

something this:

function copytonewrange(rsrc range, rdst range) dim ssub string if rsrc.columns.count > 1 or rdst.columns.count > 1 copytonewrange = cverr(xlerrvalue) elseif rsrc.rows.count <> rdst.rows.count copytonewrange = cverr(xlerrvalue) else ssub = "copytonewrangesub(" & _ rsrc.address(true, true, xla1, true) & "," & _ rdst.address(true, true, xla1, true) & ")" rsrc.worksheet.evaluate ssub copytonewrange = vbnullstring end if end function sub copytonewrangesub(rsrc range, rdst range) rdst.value = rsrc.value end sub

note, there several other issues in code have addressed

when want function homecoming err, homecoming ... error to ensure ranges shaped correctly, counting cells lone not enough don't loop on range, re-create in 1 go. your function should homecoming something you should dim variables (use alternative explicit forcefulness this) use meaningfull parameter names

thanks tim willaims concept

excel-vba