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


--
icdoo
------------------------------------------------------------------------
icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342
View this thread: http://www.excelforum.com/showthread...hreadid=497095

Data with starting with - causes Type Mismatch
 

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

Data with starting with - causes Type Mismatch
 

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

Data with starting with - causes Type Mismatch
 

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