VBA Cut and Paste Help
 

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

VBA Cut and Paste Help
 

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


--
Regards,
Tom Ogilvy


"Ray Batig" wrote in message
link.net...
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

VBA Cut and Paste Help
 

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

Tom Ogilvy wrote in message
...
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


--
Regards,
Tom Ogilvy


"Ray Batig" wrote in message
link.net...
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