Click here to Skip to main content
15,116,815 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello I hope this will make sense,

I have found this script that if a letter or number is in one cell it will input a value in the another cell

I was wondering how I could add multiple rules to this.At the moment if C or 9 is entered in a cell then it produces the sentence "word here will show up"

I want it to show up in a different cell not the adjacent one, and also I want to be able to add different letter to the list to output different values.

EG

Cell Has This in Column A       Output when Macro runs in Column E

C or 9                                           "word here will show up"

HELLO OR GOODBYE                               "Word1"

Pink or Yellow                                           "Word2"

Etc, Any help would be great and appreciated as I am very new to VB and Macros

Sub CheckValues()
    Application.ScreenUpdating = False
    With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
        .Formula = "=IF(MIN(FIND({""C"",9},A2&""C9""))<=LEN(A2),""word here will show up"","""")"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub
Posted
Comments
RedDk 22-Jul-15 16:06pm
   
Well, thanks for being honest. This script you've obtained is a touch on the funky side. I can run it. But not without resolving scope issues using Worksheets().Range( ... etc). That said, perhaps the issue you have is with what the meaning of each of the functions in the .FORMULA() clause actually mean? In this case I'd select "Excel Help" and look up each in VBA Lang Ref.

Tip:

Sub formulaCreate4()
Worksheets("Sheet6").Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1).Formula = "=IF(MIN(FIND({""C"",9,""log""},A2&""C9log""))<=LEN(A2),""* * * * *"","""")"
End Sub

Put this in a module and F8 through it to see what the macro modification does.

Hope it helps.

1 solution

replace
VB
...Offset(, 1)
with
VB
...Offset(, 4)
to get output in column E

For your multiple condition:
your need to build a formula that match your needs.
Actual formula is in the form:
IF(Condition1, Answer1, empty)

for 3 conditions you need something like:
IF(Condition1, Answer1, IF(Condition2, Answer2, IF(Condition3, Answer3, empty)))
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900