instr issue with matching string in another string

[ExcelEveryday1]

Hi guys,

Instr doesnt seem to be matching properly for me, and I dont know what Im doing wrong. I get a match if my criteria value is the same as my search location value, but if the string is in the other string without matching it exactly, my code doesnt pick up on it.

----------------------------------------
dim cell as range
dim range1 as range
dim ws as worksheet

For Each cell In range1


If InStr(ws.Cells(10036, 4).Value, cell.Value) <> 0 Then
ws.Cells(10036, 2).Value = "freedom"
Else
End If
Next cell
-------------------------------------------

As an example of what I'm looking for:

(ws.Cells(10036, 4) = 2ABC_123
cell.value = "2ABC"

I also tried adding in an & "*" to my cell.value, but that didnt seem to help


Thanks

 

[ExcelEveryday1]

Alright! So I didnt have worksheet set ranges. I did that, and now it all works. Thanks for your help Eric!

 

[ExcelEveryday1]

Wait. If activesheet is not ws3, then my set ranges give me this error "Method range of object" worksheet failed. I could solve this by ws3.activate, but I would like know why this is happening?!

 

[Eric W]

This is your Set statement?

dim ws3 as worksheet
set ws3 as thisworkbook.sheets(3)

The second line should be:

Set ws3 = Thisworkbook.Sheets(3)

Also, are you sure that Sheets(3) is the one you want? If you've renamed or rearranged sheets, it might be better to use a name:

Set ws3 = Thisworkbook.Sheets("Sheet3")

 

[ExcelEveryday1]

This is your Set statement?

dim ws3 as worksheet
set ws3 as thisworkbook.sheets(3)

The second line should be:

Set ws3 = Thisworkbook.Sheets(3)

Also, are you sure that Sheets(3) is the one you want? If you've renamed or rearranged sheets, it might be better to use a name:

Set ws3 = Thisworkbook.Sheets("Sheet3")

Good Point. I actually I have the sheet I want in in sheets("") format, I just changed it due to privacy concerns and it was the first thing that came to mind.

so in my statements I have

public range1 as range
------
dim ws3 as worksheet
Set ws3 = ThisWorkbook.Sheets("3")

Set Range1 = ws3.range(Cells(1, 5), Cells(2, 6))

------------

This format gives me the error mentioned above if I dont have ws3 as activesheet.
If I ws3.activate before setting my ws3.regions then it works.

 

[Andrew Poulsom]

The Cells property needs to be qualified just like the Range property:

Set Range1 = ws3.Range(ws3.Cells(1, 5), ws3.Cells(2, 6))

 

[Eric W]

Andrew makes an excellent point. I still miss that sometimes. If you want, you can use a With statement:

With ws3
    Set Range1 = .Range(.Cells(1,5), .Cells(2,6))
End With

Notice the . before Range and Cells - this is an indication to VBA to use the object defined on the With statement. This is equivalent to what Andrew wrote. It's often a matter of personal style, although depending on the situation, one or the other can be much shorter than the other.

 

[ExcelEveryday1]

Thank you both. I sure learned a lot today! I will make with statements a standard practice when setting my vars