Data with starting with - causes Type Mismatch

I wrote a little program that I use to sort alot of data until a bug finally caught up with me today.

Range("b1").Select 'selects cell b1
For i = 1 To intRowCount
If ActiveCell.Offset(0, 2) = Null Then
ActiveCell.Value = mapcell
End If

If InStr((ActiveCell.Offset(0, 2).Value), batchnumber) Then
ActiveCell.Value = mapcell
End If
Next I

Problem is the data put into one of the ActiveCell.Offset(0, 2) cells happends to be "- test" so it displays as #NAME and crashes my macro.

Any sugestions would be helpfull

If you want to start a cell with a dash, you'll either have to preformat that
cell as Text or prefix the entry with an apostrophe: '- test

If you're adding that value in your code, you can do:

with activecell.offset(0,2)
.numberformat = "@"
.value = "- test"
'or
.value = mapcell
end with

Or using the apostrophe:

activecell.offset(0,2).value = "'" & "- test"
or
activecell.offset(0,2).value = "'" & mapcell

=====
If the value is already there and you're reading it, you can either look at the
..text property of the cell (which returns the characters: #name?).

Or you can check to see if it's an error:

with activecell.offset(0,2)
if iserror(.value) then
'do what you want
else
'do what you want here, too
end if
end with

or just
if iserror(activecell.offset(0,2).value) then
'...
else
'...
end if





icdoo wrote:
>
> I wrote a little program that I use to sort alot of data until a bug
> finally caught up with me today.
>
> Range("b1").Select 'selects cell b1
> For i = 1 To intRowCount
> If ActiveCell.Offset(0, 2) = Null Then
> ActiveCell.Value = mapcell
> End If
>
> If InStr((ActiveCell.Offset(0, 2).Value), batchnumber) Then
> ActiveCell.Value = mapcell
> End If
> Next I
>
> Problem is the data put into one of the ActiveCell.Offset(0, 2) cells
> happends to be "- test" so it displays as #NAME and crashes my macro.
>
> Any sugestions would be helpfull
>
> --
> icdoo
> ------------------------------------------------------------------------
> icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342
> View this thread: http://www.excelforum.com/showthread...hreadid=497095

--

Dave Peterson

Dave you always save my A$$, Thanks for the solution I adapted it nicely!!!!

For i = 1 To intRowCount

If IsError(ActiveCell.Offset(0, 2).Value) Then
ActiveCell.Offset(0, 2).Select
MyColumnNumber = ActiveCell.Row
MyColumnLetter = Mid(ActiveCell.Address, 2, (InStr(2, ActiveCell.Address, "$")) - 2)
MsgBox "Error in Cell " & MyColumnLetter & MyColumnNumber & " Remove Any Math Symbols and Re-Try"

Exit Sub
Else

If InStr((ActiveCell.Offset(0, 2).Value), batchnumber) Then
ActiveCell.Value = mapcell
End If
End If
ActiveCell.Offset(1, 0).Select
Next i

The bill is in the mail! <vbg>

Glad you got it working.

icdoo wrote:
>
> Dave you always save my A$$, Thanks for the solution I adapted it
> nicely!!!!
>
> For i = 1 To intRowCount
>
> If IsError(ActiveCell.Offset(0, 2).Value) Then
> ActiveCell.Offset(0, 2).Select
> MyColumnNumber = ActiveCell.Row
> MyColumnLetter = Mid(ActiveCell.Address, 2, (InStr(2,
> ActiveCell.Address, "$")) - 2)
> MsgBox "Error in Cell " & MyColumnLetter & MyColumnNumber & "
> Remove Any Math Symbols and Re-Try"
>
> Exit Sub
> Else
>
> If InStr((ActiveCell.Offset(0, 2).Value), batchnumber) Then
> ActiveCell.Value = mapcell
> End If
> End If
> ActiveCell.Offset(1, 0).Select
> Next i
>
> --
> icdoo
> ------------------------------------------------------------------------
> icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342
> View this thread: http://www.excelforum.com/showthread...hreadid=497095

--

Dave Peterson