Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with InStr
I am trying to create a macro that looks at a string from one cell and
compares it to each cell in a list on another sheet. If the part of the string in the list cell contains the contents in the first cell, the entire contents of the list string is copied into a second area. The code I have is below... strKeyword = Sheet1.Cells(6, 2) For lngRow = 2 To Sheet2.Cells(65536, 1).End(xlUp).Row If InStr(strKeyword, Sheet2.Cells(lngRow, 1)) 0 Then Sheet1.Cells(Sheet1.Cells(65536, 2).End(xlUp).Row + 1, 2) = Sheet2.Cells(lngRow, 1) End If Next lngRow The problem I have is that the computer acts like each list item contains the contents of my search cell... What am I missing? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with InStr
For some strange reason it still is not working... Would it matter if I used
part of a word (like "attor") to find full-length terms (like "Attorney - Criminal" or "DUI Attorney")? "JLGWhiz" wrote: Your code worked OK for me, but I used simple string data in the cells. Usin InStr might allow it to read any group of data that matches your search criteria. It is difficult to tell without knowing what the actual data is. You could check it by using the tool tips feature to check the value of Cells(lngRow, 1) on each iteration of the loop by stepping through manually. That would show you what it is actually reading in the cells. Or just put a message box in the loop to show what is in that cell on each loop. "neu2me" wrote: I am trying to create a macro that looks at a string from one cell and compares it to each cell in a list on another sheet. If the part of the string in the list cell contains the contents in the first cell, the entire contents of the list string is copied into a second area. The code I have is below... strKeyword = Sheet1.Cells(6, 2) For lngRow = 2 To Sheet2.Cells(65536, 1).End(xlUp).Row If InStr(strKeyword, Sheet2.Cells(lngRow, 1)) 0 Then Sheet1.Cells(Sheet1.Cells(65536, 2).End(xlUp).Row + 1, 2) = Sheet2.Cells(lngRow, 1) End If Next lngRow The problem I have is that the computer acts like each list item contains the contents of my search cell... What am I missing? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with InStr
What I was trying to say before is that with InStr, unless you have a string
that is unique, not part of a word, but one that cannot be misread, there is a possibility that it will be misread. You could add in vbTextCompare to see if it helps any. If InStr(strKeyword, Sheet2.Cells(lngRow, 1), vbTextCompare) 0 Then Again, not being able to see what you are trying to compare or what you are comparing it to, I cannot trouble shoot the code. The basic code does what it is supposed to do, but you could be using it in the wrong environment. "neu2me" wrote in message ... For some strange reason it still is not working... Would it matter if I used part of a word (like "attor") to find full-length terms (like "Attorney - Criminal" or "DUI Attorney")? "JLGWhiz" wrote: Your code worked OK for me, but I used simple string data in the cells. Usin InStr might allow it to read any group of data that matches your search criteria. It is difficult to tell without knowing what the actual data is. You could check it by using the tool tips feature to check the value of Cells(lngRow, 1) on each iteration of the loop by stepping through manually. That would show you what it is actually reading in the cells. Or just put a message box in the loop to show what is in that cell on each loop. "neu2me" wrote: I am trying to create a macro that looks at a string from one cell and compares it to each cell in a list on another sheet. If the part of the string in the list cell contains the contents in the first cell, the entire contents of the list string is copied into a second area. The code I have is below... strKeyword = Sheet1.Cells(6, 2) For lngRow = 2 To Sheet2.Cells(65536, 1).End(xlUp).Row If InStr(strKeyword, Sheet2.Cells(lngRow, 1)) 0 Then Sheet1.Cells(Sheet1.Cells(65536, 2).End(xlUp).Row + 1, 2) = Sheet2.Cells(lngRow, 1) End If Next lngRow The problem I have is that the computer acts like each list item contains the contents of my search cell... What am I missing? |