Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type mismatch using rnge as Range with Type 8 Input Box | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Database Query -- Data Type Mismatch | Excel Discussion (Misc queries) | |||
Type Mismatch Error when getting data from another workbook | Excel Programming | |||
Varient Int data type mismatch | Excel Programming |