detect if a formula has been used in VBA

[dispelthemyth]

I am trying to detect if the "IF" function has been used so i have the following by using thhe find in Excel

Range("C16:C20").Select
Selection.Find(What:="=if(", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Does anyone know how to use this to get a result

That is if any of the cells in the range contain "=if(" then the offset(0, 1).value = 1 otherwise it becomes 2


Regards
Graeme

 

[GTO]

Try:

Sub exa()
Dim rng As Range, rCell As Range
    
    Set rng = Range("C16:C20")
    
    For Each rCell In rng
        If rCell.HasFormula Then
            If rCell.Formula Like "=IF(*" Then
                rCell.Offset(, 1).Value = 1
            Else
                rCell.Offset(, 1).Value = 2
            End If
        End If
    Next
    
End Sub

 

[GTO]

I may have mis-read. If you also want a 2 returned if there is no formula whatsoever, add the same Else to the outer If.

 

[Jon von der Heyden]

Hello Graeme

You can actually do this with a formula.

Select D16
Create a new name called HasIf
Refers to: =ISNUMBER(FIND("IF(",GET.CELL(6,!C16)))

Now, in D16 enter: =IF(HasIf,1,2)

Copy and paste down to end of range.

 

[dispelthemyth]

Thanks for your help

I am currently splitting this out in 2


1) formula to detect the correct formula has been used

2) formula to detect the correct answer is given, obviously this is a bit flawed as it is long winded, but it does the trick

If the correct answer is given it outputs a 1, if the wrong answer is given it gives a 0

if the correct formula is used it gives a 1 and the wrong formula gives a 0

i then multiply these out, so it has 4 combinations

1 * 0 = 0
1 * 1 = 1
0 * 1 = 0
0 * 0 = 0

and the result is taken to the result page to create a % score



What i have used for one of my example answer checkers is below

'''' Question 2

Dim c As Range
Dim rng As Range, rCell As Range
    Set rng = Range("C16:C20")
    
    For Each rCell In rng
        If rCell.HasFormula Then
            If rCell.Formula Like "=IF(*" Then
                rCell.Offset(, 1).Value = 1
            Else
                rCell.Offset(, 1).Value = 0
            End If
        End If
    Next
    ''' Checks thats the answers in range c16:c20 are the correct answer as shown in range M16:M20
Sheets("Excel assessment").Select
Range("C16").Select
For Each c In Range("C16:C20")

If c.Value = c.Offset(0, 10) Then
c.Offset(0, 12).Value = 1
Else: ActiveCell.Offset(0, 13).Value = 0
End If

Next c

Range("P16") = Range("o16").Value * Range("o17").Value * Range("o18").Value * Range("o19").Value * Range("o20").Value


'' Checks that 1 of the cells in range C16:C20 ontains the IF formula

       
Range("N16").Value = Range("D16").Value * Range("D17").Value * Range("D18").Value * Range("D19").Value * Range("D20").Value

Sheets("Answers").Range("B2").Value = Range("p16").Value * Range("N16").Value

Sheets("Excel Assessment").Activate
Range("d16:D20").Clear
Range("N16:P20").Clear

 

[GTO]

Hello Graeme

You can actually do this with a formula.

Select D16
Create a new name called HasIf
Refers to: =ISNUMBER(FIND("IF(",GET.CELL(6,!C16)))

Now, in D16 enter: =IF(HasIf,1,2)

Copy and paste down to end of range.

Hi Jon,

Salute!

I would not have thought of that in a million!