PDA

View Full Version : SearchFind = Nothing in Union &/or Intersect



Blueyesdragn
03-22-2009, 07:02 PM
Greetings everyone!

I am relatively new to the world of VBA and I've managed to fix my problems by searching the net however, I've hit a brick wall with this one :banghead: and I'm hoping that there is a way around it....

I am using Excel 2007 when @ home but this needs to work with Excel 2003 since its the version we use @ work. The following procedure was created in order to retrieve data that are related to my department. The raw data contains 34 Columns and up to 40,000 rows. Using a SearchFind function found on the net (which Rocks btw), I retrieve the data that I need in less then 5 seconds, the second step is to colour code & add the name of the person responsible for that account in Column AI all based on the customer name, this works fine until....... A customer name is not in the data, the string then becomes NOTHING and any intersect or Union will fail to comply... Sadly, they need to work, since the union can contain up to 10 strings, therefore, an exit Sub is not an option for me...

Here's the Coding and the function I am using. Note that this is a partial code, since this is only the list for two reps in my team, we are 8. I am explaining the details in the code itself...

The report is pasted in Sheet3, from there I pull the accounts I need to sheet1 then proceed to step 2 which is all in sheet1.

A big thanks in advance for any assistance...




Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional MatchCase As Boolean = False) As Range
Dim c As Range, FirstAddress As String
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False) 'Delete this term for XL2000 and earlier
If Not c Is Nothing Then
Set Find_Range = c
FirstAddress = c.Address
Do
Set Find_Range = Union(Find_Range, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Function


Sub AccountsImport()

Dim ErrMsg As String
Dim Red_Co As Range, Blue_Co As Range, Purple_Ltd As Range, Green_Ltd As Range, _
Yellow_Co As Range, Black_Ltd As Range, White_Co As Range, Apple_Co As Range, _
Lemon_Co As Range, Apple_Ltd As Range, Lemon_Ltd As Range, Orange_Co As Range, Kiwi_Co As Range

Dim x35 As String, x36 As String, x37 As String, x38 As String, x39 As String, _
x40 As String, x41 As String, x42 As String, x43 As String, x44 As String, _
x45 As String, x46 As String, x47 As String, x48 As String, x49 As String, _
x50 As String, x51 As String, x52 As String, x53 As String

On Error GoTo ErrorHandler

'''''''''''''''''''''''''
''' John Doe Accounts '''
'''''''''''''''''''''''''

x35 = "APPLE CO*"
x36 = "LEMON CO*"
x37 = "APPLE LTD*"
x38 = "LEMON LTD*"
x39 = "ORANGE CO*"
x40 = "KIWI CO*"
Set Apple_Co = Find_Range(x35, Columns("A:AI"), xlValues, xlPart).EntireRow
Set Lemon_Co = Find_Range(x36, Columns("A:AI"), xlValues, xlPart).EntireRow
Set Apple_Ltd = Find_Range(x37, Columns("A:AI"), xlValues, xlPart).EntireRow
Set Lemon_Ltd = Find_Range(x38, Columns("A:AI"), xlValues, xlPart).EntireRow
Set Orange_Co = Find_Range(x39, Columns("A:AI"), xlValues, xlPart).EntireRow
Set Kiwi_Co = Find_Range(x40, Columns("A:AI"), xlValues, xlPart).EntireRow
Set JohnDoe_Union = Application.Union(Apple_Co, Lemon_Co, Apple_Ltd, Lemon_Ltd, _
Orange_Co, Kiwi_Co)
Intersect(JohnDoe_Union, Columns("A:AI")).Interior.ColorIndex = 10
Intersect(JohnDoe_Union, Columns("AI")).Value = "John Doe"
'''''''''''''''''''''''''
''' Jane Doe Accounts '''
'''''''''''''''''''''''''

'The following code works perfectly as the one above although, if RED CO
'is not in the data (Sheet1), Error 91 will pop up, if I resume next, the whole
'block will be ignored.

x47 = "RED CO*"
x48 = "BLUE CO*"
x49 = "PURPLE LTD*"
x50 = "GREEN LTD*"
x51 = "YELLOW CO*"
x52 = "BLACK LTD*"
x53 = "WHITE CO*"
Set Red_Co = Find_Range(x47, Sheet1.Columns("A:AI"), xlValues, xlPart).EntireRow
Set Blue_Co = Find_Range(x48, Sheet1.Columns("A:AI"), xlValues, xlPart).EntireRow
Set Purple_Ltd = Find_Range(x49, Sheet1.Cells, xlValues, xlPart).EntireRow
Set Green_Ltd = Find_Range(x50, Sheet1.Cells, xlValues, xlPart).EntireRow
Set Yellow_Co = Find_Range(x51, Sheet1.Columns("A:AI"), xlValues, xlPart).EntireRow
Set Black_Ltd = Find_Range(x52, Sheet1.Columns("A:AI"), xlValues, xlPart).EntireRow
Set White_Co = Find_Range(x53, Sheet1.Columns("A:AI"), xlValues, xlPart).EntireRow
Set JaneDoe_Union = Application.Union(Red_Co, Blue_Co, Purple_Ltd, Green_Ltd, _
Yellow_Co, Black_Ltd, White_Co)
Intersect(JaneDoe_Union, Columns("A:AI")).Interior.ColorIndex = 10
Intersect(JaneDoe_Union, Columns("AI")).Value = "John Doe"


'If I remove the Union & Intersect and split them individually,
'I will get the following offset (2,0) & it will be a hit and miss
'as not all the defined accounts will be coloured nor taged with the
'rep's name.


x47 = "RED CO*"
x48 = "BLUE CO*"
x49 = "PURPLE LTD*"
x50 = "GREEN LTD*"
x51 = "YELLOW CO*"
x52 = "BLACK LTD*"
x53 = "WHITE CO*"

Set Red_Co = Find_Range(x47, Sheet1.Columns("A:AI"), xlValues, xlPart)
Set Blue_Co = Find_Range(x48, Sheet1.Columns("A:AI"), xlValues, xlPart)
Set Purple_Ltd = Find_Range(x49, Sheet1.Cells, xlValues, xlPart)
Set Green_Ltd = Find_Range(x50, Sheet1.Cells, xlValues, xlPart)
Set Yellow_Co = Find_Range(x51, Sheet1.Columns("A:AI"), xlValues, xlPart)
Set Black_Ltd = Find_Range(x52, Sheet1.Columns("A:AI"), xlValues, xlPart)
Set White_Co = Find_Range(x53, Sheet1.Columns("A:AI"), xlValues, xlPart)

Red_Co.Columns("A:AI").Interior.ColorIndex = 7
Red_Co.Columns("AI").Value = "Jane Doe"
Blue_Co.Columns("A:AI").Interior.ColorIndex = 7
Blue_Co.Columns("AI").Value = "Jane Doe"
Purple_Ltd.Columns("A:AI").Interior.ColorIndex = 7
Purple_Ltd.Columns("AI").Value = "Jane Doe"
Green_Ltd.Columns("A:AI").Interior.ColorIndex = 7
Green_Ltd.Columns("AI").Value = "Jane Doe"
Yellow_Co.Columns("A:AI").Interior.ColorIndex = 7
Yellow_Co.Columns("AI").Value = "Jane Doe"
Black_Ltd.Columns("A:AI").Interior.ColorIndex = 7
Black_Ltd.Columns("AI").Value = "Jane Doe"
White_Co.Columns("A:AI").Interior.ColorIndex = 7
White_Co.Columns("AI").Value = "Jane Doe"



ErrorHandler:
Select Case Err
Case 91 ' Customer Name not on the Report "Returns Nothing"
Resume Next
End Select
End Sub

Bob Phillips
03-23-2009, 12:51 AM
I must be missing something because I don't get what your problem is, nor where the procedure AccountsImport is used.

Blueyesdragn
03-23-2009, 08:08 AM
Hi xld, let me try this again...

If a customer is non-existant in the report, I will get Run-Time Error '91':
Object Variable not set or With Block variable not set

If I take for exemple, RED CO, I will get the error message at this line:

Set Red_Co = Find_Range(x47, Sheet1.Columns("A:AI"), xlValues, xlPart).EntireRow


Which I believe is because I am selecting the entire row once found, since the name is non-existant then nothing can be selected and the following won't work either:

Set JaneDoe_Union = Application.Union(Red_Co, Blue_Co, Purple_Ltd, Green_Ltd, _
Yellow_Co, Black_Ltd, White_Co)
Intersect(JaneDoe_Union, Columns("A:AI")).Interior.ColorIndex = 10
Intersect(JaneDoe_Union, Columns("AI")).Value = "John Doe"


Since the report is an unbilled customer report showing all the records to be billed by the end of the month, these customer will be removed from the report as the month goes by and the next time it's ran (Oracle Discoverer) the list will get smaller and smaller. Therefore, even if a customer is not on the report, I need the rest to work, I've tried without a union (individually) but, the result is far from being good...

If the search/find result is Nothing, is there anyway I can change it?

As for your second question, I am not sure I understand it. I could attach the spreadsheet I am using but since I have to respect our privacy clause, I will need to also change the customer listing to dummy names, let me know if you really need it.