Searching Named Range for a specific Value

[Mozzz]

I have two sheets I am working with. Sheets(1) Column one contains the list of items I want to compare to a Named Range I have on Sheets(2).

Here is the code I am trying to use. It seems like I am not getting to the Named Range on Sheets(2) I keep getting an "Object Variable or With Block Variable not Set"

Here is my code:

Sub PostFormulas()
'   Search NameRange for EmployeeName if Error Go to next line
    Dim Found As Boolean
    Found = False
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 11 To FinalRow
        MyVariable = Cells(i, 1)
        If Sheets(2).Range("Dept_rng").Find(What:=MyVariable) = True Then
           Sheets(2).Range("L2:O2").Copy Destination:=Sheets(1).Cells(i, 10)
            
        End If
        
    Next i
        
End Sub

My activeSheet is Sheets(1)

Suggestions.. Please

Mozzz

 

[bensonsearch]

I have two sheets I am working with. Sheets(1) Column one contains the list of items I want to compare to a Named Range I have on Sheets(2).

Here is the code I am trying to use. It seems like I am not getting to the Named Range on Sheets(2) I keep getting an "Object Variable or With Block Variable not Set"

Here is my code:

Sub PostFormulas()
'   Search NameRange for EmployeeName if Error Go to next line
    Dim Found As Boolean
    Found = False
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 
    For i = 11 To FinalRow
        MyVariable = Cells(i, 1)
        If Sheets(2).Range("Dept_rng").Find(What:=MyVariable) = True Then
           Sheets(2).Range("L2:O2").Copy Destination:=Sheets(1).Cells(i, 10)
 
        End If
 
    Next i
 
End Sub

My activeSheet is Sheets(1)

Suggestions.. Please

Mozzz

does Sheets(2).Range("Dept_rng"). contain a list of true and false? if not then this will always return false as far as im reading it. have you tried using vlookup instead of find?

 

[Mozzz]

I have but it seems that I should be able to grab a cell value on sheet(1) see if it can be found on a range located in Sheet(2) If it can be located in Sheet(2) in Name Range "Dept_rng" have it copy a forumula on Sheet(2) to Sheet(1).

I tried this, thought is would work:

[CODE For i = 11 To FinalRow
MyVariable = Cells(i, 1)
If Sheets(2).Range("Dept_rng").Find(What:=MyVariable) = MyVariable Then
Sheets(2).Range("L2:O2").Copy Destination:=Sheets(1).Cells(i, 10)

End If][/CODE]

Comes back with the same error code. I know if I use Vlookup I will have to address the error code when the value is not found. I download Excel Jenie last night but have no idea how to use it.

 

[wigi]

- you'll need a line like "On Error Resume Next" to skip error messages if the MyVariable could not be found on sheet 2

- also pay attention to the arguments of the Find method: do you want to search in the full cell contents, in values or formulas, and so on.

 

[Mozzz]

Are you referring to if I use vlookup or would On Error Resume Next in front of the For Next statement work?
I will want it to be an exact match. Don't care about the case. So it should match the text value regardless of case so I think both are defaults so did not realize I needed to assign them.

 

[wigi]

Are you referring to if I use vlookup or would On Error Resume Next in front of the For Next statement work?

Use (short version):

On Error Resume Next
For ...
   Find()
Next

I will want it to be an exact match. Don't care about the case. So it should match the text value regardless of case so I think both are defaults so did not realize I needed to assign them.

These settings for Find are retained from the previous run of the Find function. So if you use Find (in code or in the worksheet) with searching for a partial match, your code will also...

 

[Mozzz]

Changed it to this:
If Sheets(2).Range("Dept_rng").Find(What:=MyVariable, LookAt:=xlWhole) = MyVariable Then
the xlWhole doesn't seem to be forcing an exact match, am I doing something wrong. It is starting to work but I am trying to make it more specific to the match of Cells(i, PickColumn) Thanks for the help you have already given me

 

[wigi]

It is starting to work

Then I guess your tests were not waterproof, since the xlWhole should just work.

Also, add the LookIn:=xlValues to the Find function arguments.