Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg: 'No Cells were found'
I'm trying to build an array where the start of each
element will be defined by a particular value in Col 6. In the following code I receive the above error message on the line marked *** For Each ws In Workbooks(SourceWorkbook).Worksheets() With ws Shtname = ws.Name Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _ = "COVER" Or UCase(.Name) = "CONTENTS" Or _ UCase(.Name) = "SUMMARY") Then *** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next etc As far as I can see with the test workbook, there are instances of '£' in Col 6 in every worksheet. Why does the code appear to fail to find '£', please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg: 'No Cells were found'
Yes, qualifying worked.
Thanks also for the test advice. Regards. "J.E. McGimpsey" wrote in message ... For one thing, you're not qualifying Columns(6) with the worksheet. That shouldn't cause the error if your activesheet column 6 has text - does it? Try: Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues) Also, I've found it's nearly always worth testing the results of SpecialCells" Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues) If rng is Nothing Then <handle no text found Else <your existing code End If In article , "Stuart" wrote: I'm trying to build an array where the start of each element will be defined by a particular value in Col 6. In the following code I receive the above error message on the line marked *** For Each ws In Workbooks(SourceWorkbook).Worksheets() With ws Shtname = ws.Name Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _ = "COVER" Or UCase(.Name) = "CONTENTS" Or _ UCase(.Name) = "SUMMARY") Then *** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next etc As far as I can see with the test workbook, there are instances of '£' in Col 6 in every worksheet. Why does the code appear to fail to find '£', please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg: 'No Cells were found'
Stuart
why do you believe the routine is not finding "£" signs ? What do you next that doesn't work as you expect it to ? The following extract from your code finds "£" signs in column 6 ... but each entry in the array just contains a "£" sign. Is that what you expect ? Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next In my test I have four cells with values, only three of them "£" signs. I ended up with an array of three elements, each containing a "£" sign. Doesn't seem that useful an exercise but maybe I've just taken it out of context ? If the cell contains anything besides the "£" sign it will be ignored, for example, "£x" Maybe you need: If Cell.Value Like "£*" Then Another thought: if the contents of the cell is interpreted as a monetary value then I don't think SpecialCells(xlConstants, xlTextValues) picks it up. As you are running this across all the worksheets, how do you plan to use the information gathered ? I think you need to step through your code to see what cells are included in rng and how they are handled. Regards Trevor "Stuart" wrote in message ... I'm trying to build an array where the start of each element will be defined by a particular value in Col 6. In the following code I receive the above error message on the line marked *** For Each ws In Workbooks(SourceWorkbook).Worksheets() With ws Shtname = ws.Name Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _ = "COVER" Or UCase(.Name) = "CONTENTS" Or _ UCase(.Name) = "SUMMARY") Then *** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next etc As far as I can see with the test workbook, there are instances of '£' in Col 6 in every worksheet. Why does the code appear to fail to find '£', please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg: 'No Cells were found'
Just to add to J.E.'s advice,
Testing won't help if you never get past the set statement. When cells matching the criteria are not found, that raises a trappable error so it would be On Error Resume Next set rng = .Columns(6).SpecialCells(xlConstants, xlTextValues) On Error goto 0 if not rng is nothing then 'cells were found else ' cells were not found End if Don't get carried away with the On Error Resume next - nest it around the command as shown - that is where it is needed. You have been down that road before. -- Regards, Tom Ogilvy Stuart wrote in message ... Yes, qualifying worked. Thanks also for the test advice. Regards. "J.E. McGimpsey" wrote in message ... For one thing, you're not qualifying Columns(6) with the worksheet. That shouldn't cause the error if your activesheet column 6 has text - does it? Try: Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues) Also, I've found it's nearly always worth testing the results of SpecialCells" Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues) If rng is Nothing Then <handle no text found Else <your existing code End If In article , "Stuart" wrote: I'm trying to build an array where the start of each element will be defined by a particular value in Col 6. In the following code I receive the above error message on the line marked *** For Each ws In Workbooks(SourceWorkbook).Worksheets() With ws Shtname = ws.Name Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _ = "COVER" Or UCase(.Name) = "CONTENTS" Or _ UCase(.Name) = "SUMMARY") Then *** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next etc As far as I can see with the test workbook, there are instances of '£' in Col 6 in every worksheet. Why does the code appear to fail to find '£', please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg: 'No Cells were found'
One thing I didn't include is that you need to have an On Error
Resume Next...On Error Goto (or a pointer to your error handling routine) bracketing the call to SpecialCells, othewise Specialcells can still throw a run-time error. In article , "Stuart" wrote: Yes, qualifying worked. Thanks also for the test advice. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg: 'No Cells were found'
Yes, I remember that one!
Regards. "Tom Ogilvy" wrote in message ... Just to add to J.E.'s advice, Testing won't help if you never get past the set statement. When cells matching the criteria are not found, that raises a trappable error so it would be On Error Resume Next set rng = .Columns(6).SpecialCells(xlConstants, xlTextValues) On Error goto 0 if not rng is nothing then 'cells were found else ' cells were not found End if Don't get carried away with the On Error Resume next - nest it around the command as shown - that is where it is needed. You have been down that road before. -- Regards, Tom Ogilvy Stuart wrote in message ... Yes, qualifying worked. Thanks also for the test advice. Regards. "J.E. McGimpsey" wrote in message ... For one thing, you're not qualifying Columns(6) with the worksheet. That shouldn't cause the error if your activesheet column 6 has text - does it? Try: Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues) Also, I've found it's nearly always worth testing the results of SpecialCells" Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues) If rng is Nothing Then <handle no text found Else <your existing code End If In article , "Stuart" wrote: I'm trying to build an array where the start of each element will be defined by a particular value in Col 6. In the following code I receive the above error message on the line marked *** For Each ws In Workbooks(SourceWorkbook).Worksheets() With ws Shtname = ws.Name Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _ = "COVER" Or UCase(.Name) = "CONTENTS" Or _ UCase(.Name) = "SUMMARY") Then *** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next etc As far as I can see with the test workbook, there are instances of '£' in Col 6 in every worksheet. Why does the code appear to fail to find '£', please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Msg: 'No Cells were found'
Thanks for the reply.
I've taken the problem a little further, at least as far as my limited knowledge allows. My latest post explains what I've been using the array for.......ie to collect the 'page' start instances in col(6). Regards and thanks. "Trevor Shuttleworth" wrote in message ... Stuart why do you believe the routine is not finding "£" signs ? What do you next that doesn't work as you expect it to ? The following extract from your code finds "£" signs in column 6 ... but each entry in the array just contains a "£" sign. Is that what you expect ? Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next In my test I have four cells with values, only three of them "£" signs. I ended up with an array of three elements, each containing a "£" sign. Doesn't seem that useful an exercise but maybe I've just taken it out of context ? If the cell contains anything besides the "£" sign it will be ignored, for example, "£x" Maybe you need: If Cell.Value Like "£*" Then Another thought: if the contents of the cell is interpreted as a monetary value then I don't think SpecialCells(xlConstants, xlTextValues) picks it up. As you are running this across all the worksheets, how do you plan to use the information gathered ? I think you need to step through your code to see what cells are included in rng and how they are handled. Regards Trevor "Stuart" wrote in message ... I'm trying to build an array where the start of each element will be defined by a particular value in Col 6. In the following code I receive the above error message on the line marked *** For Each ws In Workbooks(SourceWorkbook).Worksheets() With ws Shtname = ws.Name Dim i As Long Dim varr() Dim rng As Range, Cell As Range, rng1 As Range ReDim varr(1 To 1) If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _ = "COVER" Or UCase(.Name) = "CONTENTS" Or _ UCase(.Name) = "SUMMARY") Then *** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "£" Then If IsEmpty(varr(1)) Then Set varr(1) = Cell Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = Cell End If End If Next etc As far as I can see with the test workbook, there are instances of '£' in Col 6 in every worksheet. Why does the code appear to fail to find '£', please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Program.xls cannot be found error | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
error "module not found" | Excel Discussion (Misc queries) | |||
The search key was not found error | New Users to Excel | |||
Error; chart wasn't found | Excel Discussion (Misc queries) |