Find, xlWhole and xlPart as variables

I

izzy117

New Member
Joined
Feb 13, 2008
Messages
41
Good morning and happy Friday!

Question: Is there a way to make xlPart and xlWhole variable?
I do not know how to do that so i am attempting to do what you see below.


I am running into an error with this code section below. Error 91: "Object variable or With block variable not set"
Code: 
        If Sheets("Dashboard").Range("A2").Value = 1 Then
            Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)  [COLOR=#00ff00]'Errors here[/COLOR]
            Else
            Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart)
        End If
I am trying to use Find to locate an exact match or a partial match depending on what my user knows.
When i am not using an IF statement the code works fine...
Only using
Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)



Here is everything because i am always reading you guys ask that of people :-)
Code: 
Sub GatherAll()
    Dim rngFind As Range
    Dim rngPicked As Range
    Dim rngLook As Range
    Dim WhereiGo As Range
    Dim strValueToPick As String
    Dim strFirstAddress As String
    Dim MyShort As String
    
    MyShort = ActiveCell.Value
    
    If ActiveCell.Address = Range("A4") Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7").End(xlDown).End(xlDown).Offset(0, -3), Sheets("Database").Range("A7"))
    ElseIf ActiveCell.Address = Range("B4") Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7").End(xlDown).End(xlDown).Offset(0, -2), Sheets("Database").Range("B7"))
    ElseIf ActiveCell.Address = Range("C4") Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7").End(xlDown).Offset(0, 2), Sheets("Database").Range("F7"))
    End If
  

    With rngLook
    
        If Sheets("Dashboard").Range("A2").Value = 1 Then
            Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
            Else
            Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart)
        End If
        
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Set rngPicked = rngFind
            Do
                Set rngPicked = Union(rngPicked, rngFind)
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
        End If
    End With
    
    If Not rngPicked Is Nothing Then
        Set WhereiGo = Sheets("Dashbaord").Range("A65532").End(xlUp).Offset(1)
        rngPicked.Columns("A:B").Copy Destination:=WhereiGo
        'Some other columns.copy Destination:=WhereiGo.Offset(0, 3)  ...whatever
        'Some other columns.copy Destination:=WhereiGo.Offset(0, 12) ...whatever
    End If


End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Hi,
you should not need a variable - xllookAt enumeration constant gives choice of:

xlwhole - returns value of 1
xlPart -returns value of 2

Providing your cell value is 1 (xlWhole) or 2 (xlPart) you should be able to do what you want like this:

Code: 
Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, Lookat:=Sheets("Dashboard").Range("A2").Value)

Dave
 
Upvote 0
Hi

I'd say that the variable rngLook is not initialised.

I see you compare the address of the Activecell with the values in cells A4:C4

If the address is not any of those 3 then the variable rngLook is not initialised.


Before the "With rngLook" confirm that the variable rngLook refers to a range.
 
Upvote 0
pgc01,
I am using a worksheet change event. So this will only run if one of those three cells is changed. Those are search fields for my users, Part Number, Description, Customer. Thus the variable ranges to search.


As to the xlWhole and xlPart, one user might remember the term "apple" but the full part number could be "Green-Apple-002" or some such. There can be 20 results with "apple" in the part number or description.
Fiona-Apple-Star
Green-Apple-Small
Green-Apple-001
Red-Apple-001 ...

So step one is to Find xlPart the user can remember. Step two would be to select the cell of their desired item and run a macro to give exact results. Step two is not needed if in step one we know the full xlWhole.

//

dmt32,
Thanks for the great idea! I got all excited and tried implementing then ran into new error on the same line of code.
I've been using this block of code for years. This is just a new variation for me. So, i'll keep banging away at it until i can come back to ask a more precise question.
 
Upvote 0
izzy117 said:
pgc01,
I am using a worksheet change event. So this will only run if one of those three cells is changed. Those are search fields for my users, Part Number, Description, Customer. Thus the variable ranges to search.
Click to expand...

I really don't understand.

You say that one of the 3 cells changes and then the value of one of them is the address of the activecell?
Sorry, makes no sense to me.

Also I don't see why the active cell.
When the change event runs it checks the cell that changed, not the active cell (which can be the cell that changed or not).

I can take a guess.
You want to set the value of rngLook depending on which of the 3 cells changed. For that you can send its address from the change event procedure (that from what I understood calls GatherAll().

Anyway, as a quick and easy test, just check the address of rngLook before the With

Code: 
    ...
End If

[I]MsgBox rngLook.Address[/I]

With rngLook

...
 
Last edited:
Upvote 0
Thanks, pgc01! I'll give that a shot to help track down issues.



Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A4:C4")) Is Nothing Then Exit Sub
Sheets("Dashboard").Range("A24:O20000").ClearContents
If Target.Value = "" Then 'We want to see nothing.
    Sheets("DashBoard").Range("A4:C4").ClearContents
    Exit Sub
End If

'User search fields
If Target.Address = "C4" Then
    Sheets("Dashboard").Range("A4:B4").ClearContents
    Sheets("Dashboard").Range("C4").Select
    'Application.Run "Dashboard.xlsm!???????" [COLOR=#0000ff]'Find Customer name[/COLOR]
ElseIf Target.Address = "B4" Then
    Sheets("Dashboard").Range("A4,C4").ClearContents
    Sheets("Dashboard").Range("B4").Select
    'Application.Run "Dashboard.xlsm!???????" [COLOR=#0000ff]'Find Description[/COLOR]
ElseIf Target.Address = "A4" Then
    Sheets("Dashboard").Range("B4,C4").ClearContents
    Sheets("Dashboard").Range("A4").Select
    'Application.Run "Dashboard.xlsm!???????" [COLOR=#0000ff]'Find Part Number[/COLOR]
End If

Application.Run "Dashboard.xlsm!GatherAll"   'Only having one macro makes sense.


and so on...
 
Upvote 0
Your on the right track now.

Target is the cell(s) that changed, and that's the one to test.

A remark: Address without parameters returns the absolute address

Should be, for ex.:

Code: 
If Target.Address = "$C$4" Then
 
Upvote 0
Treat as RESOLVED because everything is working. But any tips are welcome :-D
//

dmt32,
I could not get this to work.
Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, Lookat:=Sheets("Dashboard").Range("A2").Value)

I also tried variations such as,
Lookat:=Sheets("Dashboard").Range("B2").Value)
With this formula on B2: IF(A2 = 1,"xlWhole","xlPart")
(A2 is the target of radio buttons which put in a 1 or 2)
... and,
dmt = Sheets("Dashboard").Range("A2").Value
dmt = Sheets("Dashboard").Range("B2").Value
Lookat:=dmt)
Lookat:=(dmt))
...and then,
I took out the formula from B2 and just typed in text xlPart to see if that would help using this.
Lookat:=Sheets("Dashboard").Range("B2").Text)

No luck. Maybe i'm just holding it wrong. :confused:
Just to be clear i did all this after correcting the address issue as noted below.


Thanks to pgc01 for reminding me to check my work. Just because vba gives an error in one place does not mean that is where the error starts.
So i found that my "rngLook" range was blank because i was not comparing an address to an address. So i fixed that as marked in red.
Code: 
    If ActiveCell.Address = Sheets("Dashboard").Range("A4").[COLOR=#ff0000]Address [/COLOR]Then
    Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7").End(xlDown).End(xlDown).Offset(0, -3), Sheets("Database").Range("A7"))
    ElseIf ActiveCell.Address = Sheets("Dashboard").Range("B4").[COLOR=#ff0000]Address [/COLOR]Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7").End(xlDown).End(xlDown).Offset(0, -2), Sheets("Database").Range("B7"))
    ElseIf ActiveCell.Address = Sheets("Dashboard").Range("C4").[COLOR=#ff0000]Address [/COLOR]Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7").End(xlDown).Offset(0, 2), Sheets("Database").Range("F7"))
    End If

    With [B]rngLook[/B]

        If Sheets("Dashboard").Range("A2").Value = 1 Then
            Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
            Else
            Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart)
        End If
...
 
Upvote 0
dmt32,
I could not get this to work.
Set rngFind = .Find(MyShort, .Cells(1, 1), LookIn:=xlValues, Lookat:=Sheets("Dashboard").Range("A2").Value)

I also tried variations such as,
Lookat:=Sheets("Dashboard").Range("B2").Value)
With this formula on B2: IF(A2 = 1,"xlWhole","xlPart")
(A2 is the target of radio buttons which put in a 1 or 2)
... and,
dmt = Sheets("Dashboard").Range("A2").Value
dmt = Sheets("Dashboard").Range("B2").Value
Lookat:=dmt)
Lookat:=(dmt))
...and then,
I took out the formula from B2 and just typed in text xlPart to see if that would help using this.
Lookat:=Sheets("Dashboard").Range("B2").Text)

No luck. Maybe i'm just holding it wrong.
Click to expand...

You have not understood my post - xlWhole & xlPart are NOT strings they are from xllookAt enumeration constant & return integer values 1 & 2 respectively.

You can test this quite simply with a msgbox

Code: 
msgbox xlWhole

this will display value of 1 NOT what you see as text xlWhole


As stated in my post to you, Providing your cell has an value of 1 or 2 (Integer - not text) then suggestion should work.

Dave
 
Last edited:
Upvote 0
Hi

I see that in the first 2 statements where you set the values of rngLook you use .End(xlDown) twice.

Code: 
    If ActiveCell.Address = Sheets("Dashboard").Range("A4").Address Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7")[COLOR=#ff0000][B].End(xlDown).End(xlDown)[/B][/COLOR].Offset(0, -3), Sheets("Database").Range("A7"))
    ElseIf ActiveCell.Address = Sheets("Dashboard").Range("B4").Address Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7")[B][COLOR=#ff0000].End(xlDown).End(xlDown)[/COLOR][/B].Offset(0, -2), Sheets("Database").Range("B7"))
    ElseIf ActiveCell.Address = Sheets("Dashboard").Range("C4").Address Then
        Set rngLook = Sheets("Database").Range(Sheets("Database").Range("D7").End(xlDown).Offset(0, 2), Sheets("Database").Range("F7"))
    End If

You don't do it in the 3rd statement.

Is that on purpose or is it a typo?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Searching a sheet and displaying on a userform
2
Replies
16
Views
274
colinharwood
C
A
  • Solved
Procedure fails on some sheets only
Replies
2
Views
53
AlexanderBB
A
B
  • Solved
Combine two routines
2 3
Replies
27
Views
440
NoSparks
N
G
  • Solved
Adapt VBA code where "Find" is equal to a MID function (etc)
Replies
6
Views
249
GTS
G
J
  • Question Question
Search folder for value and list left/right value
Replies
2
Views
214
JJCam
J

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top