Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    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. #2
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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

    PS, file up loader froze

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #8
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    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. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Was it SPICEd Eggnog (very low on the water content)?

    Steve

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2013-12-21 at 12:35.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #15
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Hello again,
    The process issue continues here,

    http://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
  •