VBA if cell contains…

[cdrobinson83]

Hello,

I am trying to write something that calls on another macro to run only if a cell contains certain text. But the text is actually a few different options; such as if the cell B2 contains apple or orange or banana then run call on the macro.

To make things more complicated, I would like to add to the rule that the tab name also does NOT contain certain text; also a list of values. So basically, I’d like the rule to be if cell B2 contains apple or orange or banana and the tab name does NOT contain carrot or corn or spinach, then call on the macro. And then move on through the rest of the current macro if the above statement is false basically. Is this a possibility?

 

[vw412]

The problem you will have to address is how the cell B2 is changed. If you are manually changing the contents of B2 then you could handle the Worksheet_Change event. But if it is changed by the action of a formula then you will need to find some other trigger to cause the macro to run.

 

[cdrobinson83]

Hello,

The contents of cell B2 will always be populated with data. It is not being updated or changed with a formula.

The current macro is taking data and splitting it and creating new tabs based on a row label and then copies the data to the new tab. Basically just a macro that splits data out into separate tabs so that I can then export each tab to a new individual workbook. What I’d like to do is then run another macro that updates the data on the new tab (before exporting to a new workbook) if it meets the above mentioned criteria. If it does not meet the criteria then the data is just left as it is on the new tab. I already have the macro step that I’d like to run, I just don’t know how to call for it without manually running it.

 

[Michael M]

How about

Sub MM1()
If Cells(1, 2).Value = "orange" Or Cells(1, 2).Value = "apple" Or Cells(1, 2).Value = "banana" Then
    If ActiveSheet.Name <> "orange" And ActiveSheet.Name <> "carrot" And ActiveSheet.Name <> "spinach" Then
    Call macro
    End If
End If
    
End Sub

 

[vw412]

what is the trigger you want to use to cause the macro to run? A button on the sheet would still require you to manually click it. Is there some timer to check? Right now, how do you decide when to manually run the macro? that may give a hint as to how to automate it.

 

[cdrobinson83]

How about

Sub MM1()
If Cells(1, 2).Value = "orange" Or Cells(1, 2).Value = "apple" Or Cells(1, 2).Value = "banana" Then
    If ActiveSheet.Name <> "orange" And ActiveSheet.Name <> "carrot" And ActiveSheet.Name <> "spinach" Then
    Call macro
    End If
End If
   
End Sub

I was able to tinker with that and get the results I was looking for:

Dim sDesc As String
Range("B2").Select
sDesc = Selection

If InStr(1, Selection, "APPLE") Or InStr(1, Selection, "ORANGE") Or InStr(1, Selection, "BANANA") Then
If ActiveSheet.Name <> "CORN" And ActiveSheet.Name <> "SPINACH" And ActiveSheet.Name <> "CARROT" Then
Call FOOD_MACRO
End If
End If

 

[Michael M]

Ah, sorry, I didn't realise you were looking for a partial match in the cells.
Glad you got it sorted...
you can also remove the Select lin

Dim sDesc As String
Range("B2").Select
sDesc = Selection

can simply be

Dim sDesc As String
sDesc = Range("B2")