Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to check a lot of formulas to see if a value is being multiplied by 12. The formula is in an IF statement and looks like "B1415*12". I tried the FIND function but that requires text. How can I check a formula to see if the *12 is in it or not. If it is *8, for instance, it's an error. TYIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Select Edit | Find... or press Ctrl+F.
    Enter ~*12 in the "Find what" box. (The ~ tells Excel to treat * as a literal character instead of a wildcard.)
    You may want to click Options >> and specify that you want to search the entire workbook in the Within dropdown.
    Make sure that Formulas is selected in the Look in dropdown.
    Click "Find Next" or "Find All".

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='776193' date='20-May-2009 17:30']Select Edit | Find... or press Ctrl+F.
    Enter ~*12 in the "Find what" box. (The ~ tells Excel to treat * as a literal character instead of a wildcard.)
    You may want to click Options >> and specify that you want to search the entire workbook in the Within dropdown.
    Make sure that Formulas is selected in the Look in dropdown.
    Click "Find Next" or "Find All".[/quote]
    Is there any way I can put this in a formula to return a value I can sort on? I'd like to have this in an audit column as a permanent part of the workbook. I want some cell to tell me "there's a problem on this row"-myself or someone else.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create a custom function in the Visual Basic Editor:

    Code:
    Function TestFormula(oCell As Range) As Boolean
      If oCell.HasFormula Then
    	If InStr(oCell.Formula, "*12") > 0 Then
    	  TestFormula = True
    	End If
      End If
    End Function
    Let's say you have formulas in column A. In another cell, for example in B1, enter the formula

    =TestFormula(A1)

    and fill down.

    Alternatively, use

    =IF(TestFormula(A1),"Uh oh!", "")

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='776196' date='20-May-2009 17:53']You could create a custom function in the Visual Basic Editor:

    Code:
    Function TestFormula(oCell As Range) As Boolean
      If oCell.HasFormula Then
    	If InStr(oCell.Formula, "*12") > 0 Then
    	  TestFormula = True
    	End If
      End If
    End Function
    Let's say you have formulas in column A. In another cell, for example in B1, enter the formula

    =TestFormula(A1)

    and fill down.

    Alternatively, use

    =IF(TestFormula(A1),"Uh oh!", "")


    That's great, thanks! The Alternative is more accurate evaluation.[/quote]

Posting Permissions

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