detect if a formula has been used in VBA

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Try:

Rich (BB code):
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,807
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 16, 2006
Messages
654
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

Code:
'''' 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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,949
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top