Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
I am trying to trap #NAME? occurences in column E.
These have occurred because of the import of a text file. Excel seems to consider the cell contents to be some sort of formula, is my guess. Firstly, is there any way to get at the actual data? All I can see in the cell is #NAME?. If not, then I would like to note the address of such cells out in col S, and then clear the contents of #NAME cells. I have this: Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo ErrHandler If Not IsEmpty(C) Then x = Split(C, " ") LoopCounter = 1 For i = 0 To UBound(x) C.Offset(0, LoopCounter + 3).Value = x(i) LoopCounter = LoopCounter + 1 Next End If GoTo Line1 ErrHandler: C.Offset(0, 14).Value = C.Address C.ClearContents Line1: Next End With End Sub which works for the first time it meets #NAME? but the 2nd time it errors on the line x=Split(C, " ") with a Type mismatch error. Why will this not deal with subsequent occurrences of #NAME? please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
Stuart,
Here's an alternative way. Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo no_err If Not IsEmpty(C.Value) Then If C.Value = CVErr(xlErrName) Then C.Offset(0, 14).Value = C.Address C.ClearContents End If End If no_err: Next End With End Sub By the way, why store the address, you must know what it is because you only test column E, and you put it in the same row. I would colour the offending cell myself. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I am trying to trap #NAME? occurences in column E. These have occurred because of the import of a text file. Excel seems to consider the cell contents to be some sort of formula, is my guess. Firstly, is there any way to get at the actual data? All I can see in the cell is #NAME?. If not, then I would like to note the address of such cells out in col S, and then clear the contents of #NAME cells. I have this: Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo ErrHandler If Not IsEmpty(C) Then x = Split(C, " ") LoopCounter = 1 For i = 0 To UBound(x) C.Offset(0, LoopCounter + 3).Value = x(i) LoopCounter = LoopCounter + 1 Next End If GoTo Line1 ErrHandler: C.Offset(0, 14).Value = C.Address C.ClearContents Line1: Next End With End Sub which works for the first time it meets #NAME? but the 2nd time it errors on the line x=Split(C, " ") with a Type mismatch error. Why will this not deal with subsequent occurrences of #NAME? please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
Thanks for that, but it seems to be giving pretty much the same
problem I had. It removes #NAME? and pastes the address, and it will run past the first acceptable data ok, but trips on the second cell which contains acceptable data (ie the type of data I want to split) On that 2nd occasion I get type mismatch on the line: If C.Value = CVErr(xlErrName) Then Regards. "Bob Phillips" wrote in message ... Stuart, Here's an alternative way. Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo no_err If Not IsEmpty(C.Value) Then If C.Value = CVErr(xlErrName) Then C.Offset(0, 14).Value = C.Address C.ClearContents End If End If no_err: Next End With End Sub By the way, why store the address, you must know what it is because you only test column E, and you put it in the same row. I would colour the offending cell myself. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I am trying to trap #NAME? occurences in column E. These have occurred because of the import of a text file. Excel seems to consider the cell contents to be some sort of formula, is my guess. Firstly, is there any way to get at the actual data? All I can see in the cell is #NAME?. If not, then I would like to note the address of such cells out in col S, and then clear the contents of #NAME cells. I have this: Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo ErrHandler If Not IsEmpty(C) Then x = Split(C, " ") LoopCounter = 1 For i = 0 To UBound(x) C.Offset(0, LoopCounter + 3).Value = x(i) LoopCounter = LoopCounter + 1 Next End If GoTo Line1 ErrHandler: C.Offset(0, 14).Value = C.Address C.ClearContents Line1: Next End With End Sub which works for the first time it meets #NAME? but the 2nd time it errors on the line x=Split(C, " ") with a Type mismatch error. Why will this not deal with subsequent occurrences of #NAME? please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
Stuart,
Errors will show up when importing text files if Excel sees an invalid formula. Hyphens or equal signs in your text are a cause. Something like "=smith" or "-actual" throws an error. Try revising your import criteria to cure this. If you still get errors, then the following sample code could work. '------------------------------------------------------ Sub TestCode() Dim objRange As Range Dim objArea As Range Dim objCell As Range Set objRange = Application.Intersect(Range("E:E"), ActiveSheet.UsedRange) Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) For Each objArea In objRange.Areas For Each objCell In objArea objCell.Offset(0, 14).Value = "Err in " & objCell.Address objCell.Clear Next 'ojbCell Next 'objArea Set objCell = Nothing Set objArea = Nothing Set objRange = Nothing End Sub '------------------------------------------------------ Regards, Jim Cone San Francisco, CA "Stuart" wrote in message ... I am trying to trap #NAME? occurences in column E. These have occurred because of the import of a text file. Excel seems to consider the cell contents to be some sort of formula, is my guess. Firstly, is there any way to get at the actual data? All I can see in the cell is #NAME?. If not, then I would like to note the address of such cells out in col S, and then clear the contents of #NAME cells. I have this: Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo ErrHandler If Not IsEmpty(C) Then x = Split(C, " ") LoopCounter = 1 For i = 0 To UBound(x) C.Offset(0, LoopCounter + 3).Value = x(i) LoopCounter = LoopCounter + 1 Next End If GoTo Line1 ErrHandler: C.Offset(0, 14).Value = C.Address C.ClearContents Line1: Next End With End Sub which works for the first time it meets #NAME? but the 2nd time it errors on the line x=Split(C, " ") with a Type mismatch error. Why will this not deal with subsequent occurrences of #NAME? please? Regards. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
Can you try to catch the error when you do the import?
Maybe making that field text instead of General????? would eliminate the problem. Stuart wrote: I am trying to trap #NAME? occurences in column E. These have occurred because of the import of a text file. Excel seems to consider the cell contents to be some sort of formula, is my guess. Firstly, is there any way to get at the actual data? All I can see in the cell is #NAME?. If not, then I would like to note the address of such cells out in col S, and then clear the contents of #NAME cells. I have this: Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo ErrHandler If Not IsEmpty(C) Then x = Split(C, " ") LoopCounter = 1 For i = 0 To UBound(x) C.Offset(0, LoopCounter + 3).Value = x(i) LoopCounter = LoopCounter + 1 Next End If GoTo Line1 ErrHandler: C.Offset(0, 14).Value = C.Address C.ClearContents Line1: Next End With End Sub which works for the first time it meets #NAME? but the 2nd time it errors on the line x=Split(C, " ") with a Type mismatch error. Why will this not deal with subsequent occurrences of #NAME? please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
Many thanks
However in this case no cells are found when the line Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) runs. I've got nearly 20,000 rows of data here, so big problems at the moment !! The data has come from a pdf file via the Text Import wizard. It consists of blocks of data (generally in table format) but which are interspersed with blocks of text spread across most/all of the columns. The way this text is laid out is one main problem. The 2nd problem is that the 'tables' do not always line up. This means that I can end up with cell contents like: timber l and adjacent cell: umber. So I'm having to test cells and split, so as to move l into umber to give lumber (and timber). The formula problem with = I had spotted, but the minus sign I had not considered. Sure enough these particular #NAME cells are right in the middle of a block of cells which only contain a minus sign (presumably indicating no value in the original document). If you've any ideas? Regards and thanks. "Jim Cone" wrote in message ... Stuart, Errors will show up when importing text files if Excel sees an invalid formula. Hyphens or equal signs in your text are a cause. Something like "=smith" or "-actual" throws an error. Try revising your import criteria to cure this. If you still get errors, then the following sample code could work. '------------------------------------------------------ Sub TestCode() Dim objRange As Range Dim objArea As Range Dim objCell As Range Set objRange = Application.Intersect(Range("E:E"), ActiveSheet.UsedRange) Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) For Each objArea In objRange.Areas For Each objCell In objArea objCell.Offset(0, 14).Value = "Err in " & objCell.Address objCell.Clear Next 'ojbCell Next 'objArea Set objCell = Nothing Set objArea = Nothing Set objRange = Nothing End Sub '------------------------------------------------------ Regards, Jim Cone San Francisco, CA "Stuart" wrote in message ... I am trying to trap #NAME? occurences in column E. These have occurred because of the import of a text file. Excel seems to consider the cell contents to be some sort of formula, is my guess. Firstly, is there any way to get at the actual data? All I can see in the cell is #NAME?. If not, then I would like to note the address of such cells out in col S, and then clear the contents of #NAME cells. I have this: Sub TestSplitQtyUnit() Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer With ActiveSheet For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange) On Error GoTo ErrHandler If Not IsEmpty(C) Then x = Split(C, " ") LoopCounter = 1 For i = 0 To UBound(x) C.Offset(0, LoopCounter + 3).Value = x(i) LoopCounter = LoopCounter + 1 Next End If GoTo Line1 ErrHandler: C.Offset(0, 14).Value = C.Address C.ClearContents Line1: Next End With End Sub which works for the first time it meets #NAME? but the 2nd time it errors on the line x=Split(C, " ") with a Type mismatch error. Why will this not deal with subsequent occurrences of #NAME? please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
Stuart,
Try Dave Peterson's suggestion of importing as text only. That will probably work. Something else to try is to: import the data into one column "Trim" the data then use Data | Text to Columns Otherwise try replacing... Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) with Set objRange = objRange.SpecialCells(xlCellTypeConstants, xlErrors) Regards, Jim Cone San Francisco, CA "Stuart" wrote in message ... Many thanks However in this case no cells are found when the line Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) runs. I've got nearly 20,000 rows of data here, so big problems at the moment !! The data has come from a pdf file via the Text Import wizard. It consists of blocks of data (generally in table format) but which are interspersed with blocks of text spread across most/all of the columns. The way this text is laid out is one main problem. The 2nd problem is that the 'tables' do not always line up. This means that I can end up with cell contents like: timber l and adjacent cell: umber. So I'm having to test cells and split, so as to move l into umber to give lumber (and timber). The formula problem with = I had spotted, but the minus sign I had not considered. Sure enough these particular #NAME cells are right in the middle of a block of cells which only contain a minus sign (presumably indicating no value in the original document). If you've any ideas? Regards and thanks. "Jim Cone" wrote in message ... Stuart, Errors will show up when importing text files if Excel sees an invalid formula. Hyphens or equal signs in your text are a cause. Something like "=smith" or "-actual" throws an error. Try revising your import criteria to cure this. If you still get errors, then the following sample code could work. '------------------------------------------------------ Sub TestCode() Dim objRange As Range Dim objArea As Range Dim objCell As Range Set objRange = Application.Intersect(Range("E:E"), ActiveSheet.UsedRange) Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) For Each objArea In objRange.Areas For Each objCell In objArea objCell.Offset(0, 14).Value = "Err in " & objCell.Address objCell.Clear Next 'ojbCell Next 'objArea Set objCell = Nothing Set objArea = Nothing Set objRange = Nothing End Sub '------------------------------------------------------ Regards, Jim Cone San Francisco, CA - snip - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping #NAME? question
The code amendment works......many thanks.
Will also try your and Dave's suggestions. Thanks to you all. Regards. "Jim Cone" wrote in message ... Stuart, Try Dave Peterson's suggestion of importing as text only. That will probably work. Something else to try is to: import the data into one column "Trim" the data then use Data | Text to Columns Otherwise try replacing... Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) with Set objRange = objRange.SpecialCells(xlCellTypeConstants, xlErrors) Regards, Jim Cone San Francisco, CA "Stuart" wrote in message ... Many thanks However in this case no cells are found when the line Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) runs. I've got nearly 20,000 rows of data here, so big problems at the moment !! The data has come from a pdf file via the Text Import wizard. It consists of blocks of data (generally in table format) but which are interspersed with blocks of text spread across most/all of the columns. The way this text is laid out is one main problem. The 2nd problem is that the 'tables' do not always line up. This means that I can end up with cell contents like: timber l and adjacent cell: umber. So I'm having to test cells and split, so as to move l into umber to give lumber (and timber). The formula problem with = I had spotted, but the minus sign I had not considered. Sure enough these particular #NAME cells are right in the middle of a block of cells which only contain a minus sign (presumably indicating no value in the original document). If you've any ideas? Regards and thanks. "Jim Cone" wrote in message ... Stuart, Errors will show up when importing text files if Excel sees an invalid formula. Hyphens or equal signs in your text are a cause. Something like "=smith" or "-actual" throws an error. Try revising your import criteria to cure this. If you still get errors, then the following sample code could work. '------------------------------------------------------ Sub TestCode() Dim objRange As Range Dim objArea As Range Dim objCell As Range Set objRange = Application.Intersect(Range("E:E"), ActiveSheet.UsedRange) Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors) For Each objArea In objRange.Areas For Each objCell In objArea objCell.Offset(0, 14).Value = "Err in " & objCell.Address objCell.Clear Next 'ojbCell Next 'objArea Set objCell = Nothing Set objArea = Nothing Set objRange = Nothing End Sub '------------------------------------------------------ Regards, Jim Cone San Francisco, CA - snip - --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Trapping | Excel Discussion (Misc queries) | |||
Error Trapping from WSH | Excel Discussion (Misc queries) | |||
error trapping | Excel Discussion (Misc queries) | |||
Error Trapping | Excel Programming | |||
error trapping | Excel Programming |