Using Instr to compare strings and numbers

[Reset]

I have two lists and want to compare to find matches, but in one they are strings and the other they are values. I tried to load the numebrs into a string array but got "invalid qualifier". Here's the code:

Sub t()
Workbooks("Book1.xlsx").Activate
a = Application.WorksheetFunction.CountA(Columns(1))
ReDim d1(a) As String
For x = 1 To a
d1(x) = Cells(x, 2).Value
Next x
Workbooks("Book2.xls").Activate
b = Application.WorksheetFunction.CountA(Columns(1))
ReDim d2(b) As String
For c = 2 To b
d2(c) = Cells(c, 1)
Next c
For x = 1 To a
For y = 2 To b
If InStr(1, d2.Text, d1(x), 1) > 0 Then Rows.Interior.ColorIndex = 8
Next y
Next x
End Sub

Anyone know how I can compare? I don't want to convert the numbers stored as text in the worksheet. They are text in Book1 and numbers in Book2. Thanks.

 

[mirabeau]

Why don't you use the Cstr function in your code just for the comparisons?

That needn't cause any change in the numbers in your worksheet.

 

[Reset]

Because I am a noob and make things work through monotonous trial and error. If use the Excel help for Cstr it doesn't help me figure out how I would use it.

 

[mirabeau]

To which line(s), if any, of your code does the error message apply?

 

[Reset]

When invalid qualifier comes up it highlights the "d2" in the "d2.Text" portion in the Instr line.

 

[mirabeau]

Then perhaps try

If InStr(1, Cstr(d2(y)), d1(x), 1) > 0 Then Rows(y).Interior.ColorIndex = 8

although without knowing more about your data setup that's really just a guess.

I note you already have a line

ReDim d2(b) As String

so maybe the .Text bit is causing the trouble and just leaving that out might make the difference.

 

[Reset]

Actually both seem to work. Thanks.