Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
Why is the macro unable to identify #VALUE! as a string?
I have a macro which inputs functions in to the cells, then cuts and pastes by values (so that the function results are static). After that I wish to delete all rows with cells that have #VALUE! (as a string itself) in it, but it cannot seem to identify it. Below is a test macro to demonstrate. The Cell A1 should have #VALUE! in it. Sub test() If IsEmpty(Cells(1, 1)) Then MsgBox ("A1 empty") Else MsgBox ("A1 not empty") MsgBox Cells(1, 1).Value End If End Sub Is there any other way I can check whether a cell is containing #VALUE! as a string? Thank you. - Jay - |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
Try using IsError on the cell.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jayahn" wrote in message ... Why is the macro unable to identify #VALUE! as a string? I have a macro which inputs functions in to the cells, then cuts and pastes by values (so that the function results are static). After that I wish to delete all rows with cells that have #VALUE! (as a string itself) in it, but it cannot seem to identify it. Below is a test macro to demonstrate. The Cell A1 should have #VALUE! in it. Sub test() If IsEmpty(Cells(1, 1)) Then MsgBox ("A1 empty") Else MsgBox ("A1 not empty") MsgBox Cells(1, 1).Value End If End Sub Is there any other way I can check whether a cell is containing #VALUE! as a string? Thank you. - Jay - |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
hi
intresting concept. sometime what you see in a cell is not what is in the cell. try this. Sub test() If IsEmpty(Cells(1, 1)) Then MsgBox ("A1 empty") Else MsgBox ("A1 not empty") If IsError(Cells(1, 1)) Then Rows("1:1").ClearContents End If MsgBox Cells(1, 1).Value End If End Sub regards FSt1 "jayahn" wrote: Why is the macro unable to identify #VALUE! as a string? I have a macro which inputs functions in to the cells, then cuts and pastes by values (so that the function results are static). After that I wish to delete all rows with cells that have #VALUE! (as a string itself) in it, but it cannot seem to identify it. Below is a test macro to demonstrate. The Cell A1 should have #VALUE! in it. Sub test() If IsEmpty(Cells(1, 1)) Then MsgBox ("A1 empty") Else MsgBox ("A1 not empty") MsgBox Cells(1, 1).Value End If End Sub Is there any other way I can check whether a cell is containing #VALUE! as a string? Thank you. - Jay - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
You could check it's .text property.
with worksheets("sheet1") if isempty(.cells(1,1).value) then 'it's empty else if lcase(.cells(1,1).text) = lcase("#VALUE!") then ... jayahn wrote: Why is the macro unable to identify #VALUE! as a string? I have a macro which inputs functions in to the cells, then cuts and pastes by values (so that the function results are static). After that I wish to delete all rows with cells that have #VALUE! (as a string itself) in it, but it cannot seem to identify it. Below is a test macro to demonstrate. The Cell A1 should have #VALUE! in it. Sub test() If IsEmpty(Cells(1, 1)) Then MsgBox ("A1 empty") Else MsgBox ("A1 not empty") MsgBox Cells(1, 1).Value End If End Sub Is there any other way I can check whether a cell is containing #VALUE! as a string? Thank you. - Jay - -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
Use Cells.Text rather than Cells.Value:
Sub test() If ActiveCell.Text = "#VALUE!" Then MsgBox ("found it") End If End Sub -- Gary''s Student - gsnu200764 "jayahn" wrote: Why is the macro unable to identify #VALUE! as a string? I have a macro which inputs functions in to the cells, then cuts and pastes by values (so that the function results are static). After that I wish to delete all rows with cells that have #VALUE! (as a string itself) in it, but it cannot seem to identify it. Below is a test macro to demonstrate. The Cell A1 should have #VALUE! in it. Sub test() If IsEmpty(Cells(1, 1)) Then MsgBox ("A1 empty") Else MsgBox ("A1 not empty") MsgBox Cells(1, 1).Value End If End Sub Is there any other way I can check whether a cell is containing #VALUE! as a string? Thank you. - Jay - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
Great. Thank you all for your responses( and so quick too!).
- Jay - "jayahn" wrote: Why is the macro unable to identify #VALUE! as a string? I have a macro which inputs functions in to the cells, then cuts and pastes by values (so that the function results are static). After that I wish to delete all rows with cells that have #VALUE! (as a string itself) in it, but it cannot seem to identify it. Below is a test macro to demonstrate. The Cell A1 should have #VALUE! in it. Sub test() If IsEmpty(Cells(1, 1)) Then MsgBox ("A1 empty") Else MsgBox ("A1 not empty") MsgBox Cells(1, 1).Value End If End Sub Is there any other way I can check whether a cell is containing #VALUE! as a string? Thank you. - Jay - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|