Evaluate a Range of Cells against a Rule through Excel VBA

[macrolix]

I want to evaluate if a rule (formula) is true against a set of range. Like similar to Conditional Formatting, where we can define a formula as a rule and Excel formats the cells whenever the rule is true.

User will be allowed to enter their desired formula, specify a range and give a value which should be applied if the rule is true (instead of formatting) when they run the VBA code. I have given a sample idea of how the data would look like. Please note that the formula is variable, so I can't hardcode any formula in the VBA coding. Any suggestion would a great help. Thank you.

 

[igold]

Hi macrolix

Welcome to the MrExcel Forum.

I am not real sure of what you are trying to do. Partly because the formula in Cell B2 would evaluate to false as Cell A7 <> "Apple".

That said, you may want to do some research on Application.InputBox in VBA. This type of InputBox can be coded to accept a variety of information including Formulas and Ranges. The info can be inserted either by being typed in directly to the InputBox or inserted by using the mouse to highlight the Cell or Range that you want to use.

I hope this helps...

Application.InputBox method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[macrolix]

Hi macrolix

Welcome to the MrExcel Forum.

I am not real sure of what you are trying to do. Partly because the formula in Cell B2 would evaluate to false as Cell A7 <> "Apple".

That said, you may want to do some research on Application.InputBox in VBA. This type of InputBox can be coded to accept a variety of information including Formulas and Ranges. The info can be inserted either by being typed in directly to the InputBox or inserted by using the mouse to highlight the Cell or Range that you want to use.

I hope this helps...

Application.InputBox method (Excel)

Office VBA reference topic

docs.microsoft.com

Thanks for your reply. No, I am not looking for that. I think I should explain it little more. I want to apply the formula =AND(A7="Apple", B7<50) to each of the cell in Range C7 to C10. Like conditional formatting, where we use Formula as a Rule. When it evaluates for C7, the formula is same, but when it goes down to C8 the formula should change according to that row like "=AND(A8="Apple",B8<50)", same goes for C9 and C10. That is why I had mentioned "Yes" in cell C8.

Also I already thought of pasting the formula in cell D7 and drag it down programmatically but I wanted to know if there would be any other way for it. As I would have multiple formula also to be applied. The data I have given above is just an sample and not the real data I want to work on. In real one, I may have multiple formulas to be applied. I have attached a new image, showing Conditional Formatting Rules Manager, I am trying to get something similar to that but instead of formatting, I want values, in the example I need "Yes" to occur if the formula or rule is true. Thanks

 

[Michael M]

I can't see a way of doing that, but if you highlight from the first cell to the last where the formula is to go.....type in the formul then Press CTRL + Enter, it will fill automatically.
Other than that insert the formulas via VBA.

 

[macrolix]

Thanks to all. I have got an answer myself. I first converted the formula to R1C1 type then changed it back to A1 type while giving reference according to cell row. For multiple formulas (rule), we can use looping. Any improvement or suggestions are welcome. Thanks.

Sub ConditionalValue()

    Dim myRange As Range, Cell As Range
    Dim Formula As String, Rule As String, myValue As String, cFormula As String
    
    Set myRange = Range(Range("B3").Value)
    
    Formula = Range("B2").Value
    Rule = Application.ConvertFormula(Formula, xlA1, xlR1C1, , Range("A" & myRange.Row))
    myValue = Range("B4").Value
    
    For Each Cell In myRange
        
        cFormula = Application.ConvertFormula(Rule, xlR1C1, xlA1, , Range("A" & Cell.Row))
        
        If Application.Evaluate(cFormula) = True Then Cell.Value = myValue
        
    Next

End Sub

 

[Peter_SSs]

Rather than looping through the rows, could you do them all at once like this? (Test with a copy of your workbook)

Sub ConditionalValue_v2()
  With Range(Range("B3").Value)
    .Formula = Range("B2").Value
    .Value = .Value
    .Replace What:=True, Replacement:=Range("B4").Value
    .Replace What:=False, Replacement:=""
  End With
End Sub

 

[macrolix]

Rather than looping through the rows, could you do them all at once like this? (Test with a copy of your workbook)

Sub ConditionalValue_v2()
  With Range(Range("B3").Value)
    .Formula = Range("B2").Value
    .Value = .Value
    .Replace What:=True, Replacement:=Range("B4").Value
    .Replace What:=False, Replacement:=""
  End With
End Sub

This works brilliantly, thanks. But one down side is that, as said in my 2nd message, I would have to apply multiple formula (rule) over the same set of range (not given in the sample though). But it can be overcome by modifying little bit of this code. Thanks.

 

[Peter_SSs]

one down side is that, as said in my 2nd message, I would have to apply multiple formula (rule) over the same set of range

You haven't explained how that is to work so a bit hard to allow for. Never-the-less, perhaps this would do it?
Any unused column could be used instead of column AZ if that one doesn't suit.

Sub ConditionalValue_v3()
  With Intersect(Range(Range("B3").Value).EntireRow, Columns("AZ")) '<- Choose an unused column
    .Formula = Range("B2").Value
    .Value = .Value
    .Replace What:=True, Replacement:=Range("B4").Value
    .Replace What:=False, Replacement:=""
    .Copy
    Range(Range("B3").Value).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True
    .ClearContents
  End With
End Sub