VBA Cut and Paste Help

[Ray Batig]

Greetings,

My spreadsheet has a column with several cells which display # Value. This
error is showing up because the formula in the cell is referring to another
cell which currently has a label in it. I have a loop set up which starts at
the top of the column and goes to the bottom by incrementing. If the cell is
blank, then I skip any operation, however, if the cell contains anything (
number, label, or formula ) you cut and then past the cells value into
another cell. Everything works perfect until you get to a cell containing #
Value. Then I get a type mismatch.

For rDef = 8 to nBottom

If Worksheets("Area").Cells(rDef, refCol) <> "" Then

' Code for cut & paste

End If

Next

I replaced the If statement with:

If Worksheets("Area").Cells(rDef, refCol) <> "" Or
Worksheets("Area").Cells(rDef, refCol) = CVErr(xlErrValue) Then

Thinking that the first test would work for cell contents of numbers,
labels, or formulas, and the second test would work for those cells showing
# Value. Well, now I get a type mismatch on the second test when the cell is
empty.

Hopefully I have described this issue clearly. Any suggestions how I can
accomplish this task?

Thanks in advance for your help!

Ray

 

[Tom Ogilvy]

For rDef = 8 to nBottom
if not iserror(sheets("Area").Cells(rDef, refCol)) then
If Worksheets("Area").Cells(rDef, refCol) <> "" Then

' Code for cut & paste

End If
End if
Next

 

[Ray Batig]

Hi Tom,

Thanks for the insight.

I made this modification to get everything to work.

For rDef = 8 to nBottom
if iserror(sheets("Area").Cells(rDef, refCol)) then
goto prntCell
end if
If Worksheets("Area").Cells(rDef, refCol) <> "" Then
prntCell: ' this let me skip around the problem
' Code for cut & paste


End If
Next