Checking if cell contains a value instead of equals it

ddoctor

New Member
Joined
Aug 30, 2017
Messages
27
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

"OCC #OUTS , INC #18-2142 - MEDT - MED-Transfer"

Here's my code I'm working with now

Code:
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

<tbody> </tbody>

Thanks for any help given.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

ddoctor

New Member
Joined
Aug 30, 2017
Messages
27
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

Code:
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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051

ADVERTISEMENT

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

Code:
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:
Code:
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:
Code:
Sheets("Sheet2").range("H7:H65536")=""
 
Last edited:

ddoctor

New Member
Joined
Aug 30, 2017
Messages
27
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.

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.
 

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,908
Latest member
Getfour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top