VBA question

[jayahn]

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 -

 

[Bob Phillips]

Try using IsError on the cell.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[FSt1]

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

 

[Dave Peterson]

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
...

 

[Gary''s Student]

Use Cells.Text rather than Cells.Value:

Sub test()
If ActiveCell.Text = "#VALUE!" Then
MsgBox ("found it")
End If
End Sub

 

[jayahn]

Great. Thank you all for your responses( and so quick too!).

- Jay -