PDA

View Full Version : [SOLVED] InStr reading cells - doing something wrong



sljivan
09-26-2013, 05:21 AM
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
09-26-2013, 05:34 AM
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
09-26-2013, 06:00 AM
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. :doh:

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
09-26-2013, 06:37 AM
If you paste your example to A1, the first 0 is in cell B3. The formula might be: =IFERROR(SEARCH(B1,B2,1),"")

sljivan
09-26-2013, 10:16 AM
hej guys. Thanks a lot!