Thread: Multiple If formula with TEXT

1. Multiple If formula with TEXT

The Value in Cell C1 is always a text with a numeric

The text remains constant, only the numeric changes, but forms part of the whole text.

Normally this works fine
=IF(C1 ="text1",1,0)

But the "text1" will change to "text2" text3" "text4" as expected.

To illustrate, ( I know this is wrong, but),
how would a formula work in this scenario ?

=IF(C1 ="text1",1,0)IF(C1 ="text2",1,0)IF(C1 ="text3",1,0)IF(C1 ="text4",1,0)

Thanks

2. I think I solved it, well not me actually.

=IF(Sheet10!\$A\$4="4M TOGO",1,IF(Sheet10!\$A\$4=2,22,IF(Sheet10!\$A\$4=3,33, IF(Sheet10!\$A\$4=4,44,IF(Sheet10!\$A\$4=5,55,IF(Sheet 10!\$A\$4=4,44,IF(Sheet10!\$A\$4=5,55,IF(Sheet10!\$A\$4= 6,66,0))))))))

3. I don't understand the duplication of the 4 and 5, but a shorter (and easier to maintain) version would be:
=IF(ISNUMBER(MATCH(Sheet10!\$A\$4,{"4M TOGO",2,3,4,5,6},0)),CHOOSE(MATCH(Sheet10!\$A\$4,{"4 M TOGO",2,3,4,5,6},0),1,22,33,44,55,66),0)

This also gets around the nesting limitations. You could also use a vlookup range in excel, if you kept the list in a range.

In XL2007+, you could also use the shorter:
=IFERROR(CHOOSE(MATCH(Sheet10!\$A\$4,{"4M TOGO",2,3,4,5,6},0),1,22,33,44,55,66),0)

Steve

4. The Following User Says Thank You to sdckapr For This Useful Post:

XPDiHard (2013-12-20)

5. Thanks Steve

Took me a while to get it right,

Assuming the formula is in Cell A7

Code:
`=IF(Sheet10!\$A\$4="15M TOGO",1,IF(Sheet10!\$A\$4="12M TOGO",1,IF(Sheet10!\$A\$4="10M TOGO",1,IF(Sheet10!\$A\$4=" 8M TOGO",1,IF(Sheet10!\$A\$4=" 6M TOGO",1,IF(Sheet10!\$A\$4=" 5M TOGO",1,IF(Sheet10!\$A\$4=" 3M TOGO",1,IF(Sheet10!\$A\$4=" 2M TOGO",1,0))))))))`
What this means is, in my macro it will have a "switch" which is the value of 1 or 0

Code:
```Sub Timer_Switch()

If Sheets("Sheet10").Range("A7").Value = 1 Then
' my code
End If

End Sub```
The #M TOGO part is information from a web query and every minute the information is updated
So 8M means 8 minutes TO GO to an event.

I'll have a go at your method to,

Thanks

6. If you just want a 1/0 for code why not an OR and use true/false:
=OR(Sheet10!\$A\$4="15M TOGO",Sheet10!\$A\$4="12M TOGO",Sheet10!\$A\$4="10M TOGO",Sheet10!\$A\$4=" 8M TOGO",Sheet10!\$A\$4=" 6M TOGO",Sheet10!\$A\$4=" 5M TOGO",Sheet10!\$A\$4=" 3M TOGO",Sheet10!\$A\$4=" 2M TOGO")

and test in the macro with
If Sheets("Sheet10").Range("A7").Value Then

But then again, Why have the macro look at the 1/0, (or true/false) of a formula cell? why not have the macro look at A4 directly and do something based on what is in that cell?

Steve

7. why not have the macro look at A4 directly and do something based on what is in that cell?
I thought of that to, So far there is a lot of code in the entire Workbook, the only way I know to use macro is to have several If Then, one for each #M TOGO.
That's 1 reason for having Formula instead.

The other reason, this part whole Run the formula is only used once, then that sheet is cleared after a few other calculations.

------,
Basically;

Code:
```Sub Macro1()

Sheets("Sheet10").Range("A7").FormulaR1C1 = _
"=IF(Sheet10!R4C1=""15M TOGO"",1," & Chr(10) & " IF(Sheet10!R4C1=""12M TOGO"",1," & Chr(10) & " IF(Sheet10!R4C1=""10M TOGO"",1," & Chr(10) & " IF(Sheet10!R4C1=""8M TOGO"",1," & Chr(10) & " IF(Sheet10!R4C1=""6M TOGO"",1," & Chr(10) & " IF(Sheet10!R4C1=""5M TOGO"",1," & Chr(10) & " IF(Sheet10!R4C1=""3M TOGO"",1," & Chr(10) & " IF(Sheet10!R4C1=""2M TOGO"",1,0))))))))"

'to remove formula, but keep Value
Sheets("Sheet10").Range("A7").Value = Sheets("Sheet10").Range("A7").Value

Timer_Switch
End Sub

Sub Timer_Switch()

If Sheets("Sheet10").Range("A7").Value = 1 Then

' my code- some sort of logging or database

End If

'other code -  web query /  Loop

End Sub```

8. You could check it directly with something like this:

Code:
```With Sheets("Sheet10").Range("A4")
If .Value = "15M TOGO" Or .Value = "12M TOGO" Or _
.Value = "10M TOGO" Or .Value = " 8M TOGO" Or .Value = " 6M TOGO" _
Or .Value = " 5M TOGO" Or .Value = " 3M TOGO" Or .Value = " 2M TOGO" Then
' my code- some sort of logging or database
End If
End With```
Though with so many ORs, I would suggest using an array and look for a Match. It is simpler to maintain and should be faster:
Code:
```Dim vArray As Variant
Dim x As Integer
vArray = Array("15M TOGO", "12M TOGO", "10M TOGO", " 8M TOGO", " 6M TOGO", " 5M TOGO", " 3M TOGO", " 2M TOGO")
x = 0
On Error Resume Next
x = Application.WorksheetFunction.Match(Range("Sheet10!A4"), vArray, 0)
On Error GoTo 0
If x <> 0 Then
' my code- some sort of logging or database
End If```
Of course if you are only looking for having the "M TOGO" at the end of the cell contents, you could just use:

Code:
```If Right(UCase(Sheets("Sheet10").Range("A4")), 6) = "M TOGO" Then
' my code- some sort of logging or database
End If```
Steve

9. Solved

Thanks, this is the obvious one.

Code:
```Dim vArray As Variant
Dim x As Integer
vArray = Array("15M TOGO", "12M TOGO", "10M TOGO", " 8M TOGO", " 6M TOGO", " 5M TOGO", " 3M TOGO", " 2M TOGO")
x = 0
On Error Resume Next
x = Application.WorksheetFunction.Match(Range("Sheet10!A4"), vArray, 0)
On Error GoTo 0
If x <> 0 Then
' my code- some sort of logging or database
End If```

10. XPD,

Here's another option:
Code:
```Option Explicit

Sub Test()

Dim zSearchStr  As String

'Note: initial TOGO and missing spaces to prevent inadvertent matches

zSearchStr = "TOGO15M TOGO12M TOGO10M TOGO8M TOGO6M TOGO5M TOGO3M TOGO2M TOGO"

If InStr(zSearchStr, UCase(Range("Sheet10!A4"))) > 0 Then
' my code- some sort of logging or database
MsgBox "Found", vbOKOnly, "Results"
End If

End Sub```
HTH

11. RetiredGeek,
Searches like that can be problematic, due to partial matches. If the cell had "TOGO" in it (for example) it would be found in the search, but should not be a hit as it does not meet any of the conditions. Depending on the list, there can be other partial hits. Putting demarcators between the items can help.

Steve

12. Steve,

I woke up this morning and said OMG what did I do last night! I'm surprised you're the only one that caught me on it. Must have been that last Eggnog! Oh maybe I should lay of that. ROTFLOL.gif

13. Was it SPICEd Eggnog (very low on the water content)?

Steve

14. Hey Y'all,

OK this was bothering me because I knew I used to do this, well at least as best as I can recall.

Here's code that should always work! I'm sure Steve will read that last sentence as a challenge.

Code:
```Option Explicit

Sub Test()

Dim zSearchStr  As String

'Note: #'s used to prevent inadvertent matches

zSearchStr = "#15M TOGO#12M TOGO#10M TOGO#8M TOGO#6M TOGO#5M TOGO#3M TOGO#2M TOGO#"

If InStr(zSearchStr, "#" & UCase(Range("Sheet10!A4") & "#")) > 0 Then
' my code- some sort of logging or database
MsgBox "Found", vbOKOnly, "Results"
End If

End Sub```
HTH

15. That was exactly what I meant by adding demarcators...

But if I were doing this, I would not hard-code the list in the macro, I would create a list in a worksheet and then use a lookup (like MATCH) to search the list. I think that this would be easier to maintain.

Steve

16. Hello again,
The process issue continues here,

https://windowssecrets.com/forums/sho...693#post934693

Nearing the end of this phase of the project btw,
hopefully.

Thanks again for all your helps

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•