InStr reading cells - doing something wrong

[sljivan]

Hello guys. I am new at this and what I am trying to do is make a program which will read cells one by one and check if there is a character in it.

e.g.
we have something like this:

	A	B	C	D	E
1	ABC	DEF	GHI	JKL	MNO
2	0	0	0	0	0

and we want to read the cells and underneath them write a digit if there an K in the cell.

My code is:

Sub searchForK()

Dim cell As String


For i = 1 To 5
cell = Cells(1, i)
Cells(2, i) = InStr(1, K, cell, vbTextCompare)
Next i


End Sub

And what I get is all zeros... even though in the D2 cell there is an K. And it should have written 2. Right?

So what did I do wrong?

Thank you!

[Aflatoon]

Your code thinks K is a variable and you have the middle arguments switched - you need:

Cells(2, i) = InStr(1, cell, "K", vbTextCompare)

Of course, you don't really need code for this - formulas would be easier.

[sljivan]

Your code thinks K is a variable and you have the middle arguments switched - you need:

Of course, you don't really need code for this - formulas would be easier.

Thank you very much. I fell ashamed of how stupid mistake was.

This is only a portion of a whole program so that's why I use the code.

(but also I didn't know that excel formulas can do the same thing)

[Kenneth Hobs]

If you paste your example to A1, the first 0 is in cell B3. The formula might be: =IFERROR(SEARCH(B1,B2,1),"")

[sljivan]

hej guys. Thanks a lot!