InStr Error

[JohnKGH]

I'm just beginning but I'm not sure why the following code is wrong:

Sub Macro1()
For i = 1 To Worksheets(1).Columns.Count

If InStr(Worksheets(1).Range("G6").Offset(0, i), Worksheets(2).Range("C3")) > 0 Then
If InStr(Worksheets(1).Range("G6").Offset(0, i), Worksheets(2).Range("D3")) > 0 And InStr(Worksheets(2).Range("G6").Offset(0, i), Worksheets(2).Range("E3")) > 0 Then
Worksheets(1).Range("G:G").Offset(0, i).Copy
Worksheets(3).Range("A1").Select

ActiveCell.Paste
End If
End If
Next i
End Sub

I get an application-oriented or object-oriented error in the first instr line:

If InStr(Worksheets(1).Range("G6").Offset(0, i), Worksheets(2).Range("C3")) > 0

I don't know why???

 

[MrKowz]

Welcome to the forums!

It appears you need to tell it to use those cell values.

InStr(Worksheets(1).Range("G6").Offset(0, i)[B][COLOR=red].Value[/COLOR][/B], Worksheets(2).Range("C3")[B][COLOR=red].Value[/COLOR][/B])

 

[schielrn]

Maybre try:

If InStr(Worksheets(1).Range("G6").Offset(0, i).value, Worksheets(2).Range("C3").value) > 0 Then

Sorry I don't have access to Excel to verify that is the problem, but that is at least where I would start.

Hope that helps.

 

[JohnKGH]

Thanks for the quick reply!

I tried that but it didn't work

i think using worksheet(1).range("G6").offset(0,i) automatically refers to the value or text in the cell?

And just to debug, I tried this:

MsgBox(Worksheets(1).Range("G6").Offset(0, 1), Worksheets(2).Range("C3")) and it displayed 0 correctly, so i'm not sure why it's not working.

 

[JackDanIce]

This works fine for me:

MsgBox Worksheets(1).Columns.Count
For i = 1 To Worksheets(1).Columns.Count
If InStr(Worksheets(1).Range("A1").Offset(0, i), Worksheets(2).Range("A1")) > 0 Then
    MsgBox "Match"
Else
    MsgBox "No Match"
End If
Next i

Can you give some examples of the string comparison you are doing or layout of your sheet?

 

[schielrn]

Whats the value of those 2 cells at the time of the error? Are there any #N/A's or #VALUE's or #Div/0 Errors?

 

[JohnKGH]

The cell in worksheet2 cell C3 is "X" while the row of cells beginning with G6 in worksheet3 contains various entries like "Z 15th Alpha" and "X 10 Beta". None of the cells in row G of worksheet 3 contains N/A or weird division by 0 values. They call contain entries.

 

[JohnKGH]

Does anyone else have any thoughts on this? I'm still confused.

 

[pgc01]

Hi John
Welcome to the board

The loop variable upper limit is wrong, you cannot use Columns.Count as an offset because you'd go over the limit of columns.

For ex. in Excel 2000 you have 256 columns. Since your base columns is G (G6) which is column 7, the maximum offset you can apply to the column is 249.

In your case, in excel 2000, your code

For i = 1 To [COLOR=red][B]Worksheets(1).Columns.Count[/B][/COLOR]
 
If InStr(Worksheets(1).Range("G6").Offset(0, [COLOR=red][B]i[/B][/COLOR]), Worksheets(2).Range("C3")) > 0 Then
 
...

is translated to

For i = 1 To [COLOR=red][B]256[/B][/COLOR]
 
If InStr(Worksheets(1).Range("G6").Offset(0, [COLOR=red][B]i[/B][/COLOR]), Worksheets(2).Range("C3")) > 0 Then
 
...

Now when i gets to 250, you get

If InStr(Worksheets(1).Range("G6").Offset(0, [COLOR=red][B]250[/B][/COLOR]), Worksheets(2).Range("C3")) > 0 Then

This is not possible, excel 2000 only has 256 columns and you are trying to access column 257, you'll get an "Application-defined or object-defined error"

Please check.

 

[JohnKGH]

thanks so much! I think i'm closer to the answer:

Sub Macro1()
For i = 1 To 241
If InStr(Worksheets(1).Range("G6").Offset(0, i), Worksheets(2).Range("C3")) > 0 Then
If InStr(Worksheets(1).Range("G6").Offset(0, i), Worksheets(2).Range("D3")) > 0 And (InStr(Worksheets(1).Range("G6").Offset(0, 54), Worksheets(2).Range("E3")) > 0) Then
Worksheets(1).Range("G:G").Offset(0, i).Copy
Worksheets(4).Range("A1").Select
ActiveSheet.Paste
End If
'
End If
'
Next i
End Sub

I now get a "select method of range class failed" error for
Worksheets(4).Range("A1").Select