Checking if cell contains a value instead of equals it

[ddoctor]

I'm trying to make a spreadsheet that checks all cells in a given range on one sheet and then displays relevant information in a separate form in another sheet. Long story short I got a code to work, but it only looks for the cell value. For this examply I just want it to find "OCC" out of

[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 448"]"OCC #OUTS , INC #18-2142 - MEDT - MED-Transfer"

Here's my code I'm working with now

Sub Test()

Dim SrchRng As Range, cel As Range
Set SrchRng = Sheets("Sheet1").Range("A1:A100")
For Each cel In SrchRng
    If cel.Value = "OCC" Then
        Sheets("Sheet2").Range("B1:B1") = cel.Value
    End If
Next

End Sub

[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any help given.

 

[Roderick_E]

replace If cel.Value = "OCC" Then with if instr(cel.value, "OCC")>0 then

 

[ddoctor]

replace If cel.Value = "OCC" Then with if instr(cel.value, "OCC")>0 then

Awesome Thank you!

 

[ddoctor]

I have one more question regarding a similar code. I would have asked earlier, but it's a new problem I've run into after showing the finished product to my boss.

When using this

Private Sub CommandButton1_Click()
Dim SrchRng As Range, cel As Range
Set SrchRng = Sheets("Sheet1").Range("A1:A1000")

For Each cel In SrchRng
    If InStr(cel.Value, "STANDBY") > 0 Then
        Sheets("Sheet2").Range("H7:H8") = cel.Value
    End If
Next
End Sub

it is great when I am only looking for one unique instance, but when theirs multiple instances it will appear to scroll through them all in one cell then end on the final one. I'm needing it to put them into a list in subsequent cells, and I can't quite figure it out on my own.

Sorry for what might be a dumb question. You've been a lot of help.

 

[Roderick_E]

I have one more question regarding a similar code. I would have asked earlier, but it's a new problem I've run into after showing the finished product to my boss.

When using this

Private Sub CommandButton1_Click()
Dim SrchRng As Range, cel As Range
Set SrchRng = Sheets("Sheet1").Range("A1:A1000")

For Each cel In SrchRng
    If InStr(cel.Value, "STANDBY") > 0 Then
        Sheets("Sheet2").Range("H7:H8") = cel.Value
    End If
Next
End Sub

it is great when I am only looking for one unique instance, but when theirs multiple instances it will appear to scroll through them all in one cell then end on the final one. I'm needing it to put them into a list in subsequent cells, and I can't quite figure it out on my own.

Sorry for what might be a dumb question. You've been a lot of help.

Hi there. First, from what I can tell you must have a merged cell in H7 & H8? This is going to be a problem for doing subsequent rows.
HOWEVER, since you could have multiples of cells with the text "Standby", you will most likely not want to put it in H7. You may want to start at H7, but if you have another occurrence then you'll want that in a subsequent row, H8, then H9, H10 and on and on? If this is the case, we need to rework your code a bit:

Private Sub CommandButton1_Click()
Dim SrchRng As Range, cel As Range
dim startrow = 7 'this is the row the first matched result will populate
Set SrchRng = Sheets("Sheet1").Range("A1:A1000")
'**may want to add a clear old data line here*** see my suggestion below.
For Each cel In SrchRng
    If InStr(cel.Value, "STANDBY") > 0 Then
        Sheets("Sheet2").cells(startrow,"h") = cel.Value
startrow = startrow + 1 'we set up for the next row
    End If
Next
End Sub

Keep in mind that you can't have other data in column H or it will be overwritten when this macro runs. And if it were me, I'd clear column H7:H65536 every time you run this macro just to make sure there isn't any old data.
To do this, add this line:

Sheets("Sheet2").range("H7:H65536")=""

 

[ddoctor]

I meant to get to this much sooner, but we've been running around all day at work. The little I did get to play with this, excel did not like this line of code.

dim startrow = 7 'this is the row the first matched result will populate

I get a compile error: Expected end of statement. I'm sure it's something done wrong on my end. I'll get around to messing with it tonight.

If I change dim to set it seems to make it happier until it's actually ran, but then I get an object required error.

Otherwise I think it'll work, at the very least what you're saying to do makes sense.

 

[Dryver14]

dim startrow as long
startrow = 7

 

[ddoctor]

dim startrow as long
startrow = 7

This all works great as expected. Thank you for your time.