Use a cell value (text) as a part of a formula in Excel VBA

Question

I have a problem while doing a macro in Excel VBA which looks simple but I was not able to find an answer.

What I want is to change a formula depending on a value of a concrete cell; the cell is situated in C7 and can have the text OR or the text AND. The part of the formula is (being CritEUs and CritSKUs String variables):

If CritEUs = "NO" (OR/AND) CritSKUs = "NO" Then .... (whatever)

So I want to change the OR/AND depending on the value in C7, I tried to use INDIRECT but I think it works only with numbers, and also the following (being W the Worksheet variable):

Dim Pattern As String

Pattern = W.Range("C7").Value

If CritEUs = "NO" " & Pattern & " CritSKUs = "NO" Then 

But the Excel don't accept me this option.

Could it be that this is not possible?

I would really appreciate any help!

Answer 1

I'd look to handle this in another if statement and then nest the next if statement within like so:

Sub Example()

Dim Pattern As String

Pattern = W.Range("C7").Value

If Pattern = "AND" Then
    If CritEUs = "NO" And CritSKUs = "NO" Then
        'Do Something'
    End If
ElseIf Pattern = "OR" Then
    If CritEUs = "NO" Or CritSKUs = "NO" Then
        'Do Something'
    End If
End If

End Sub