Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
'paste special', 'paste link' formatting transfer | Excel Discussion (Misc queries) | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Macro to Paste to specific line, and continue to Paste each time on next row not over | Excel Programming |