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 -

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 -

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 -

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

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 -

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 -