Use Variable in AND formula in excel

[Gonzalo De La Torre]

Hello everyone,

I would like to know what is the syntax to retrieve the value of a variable and use it in an AND formula in excel.

This is my current code:

Sub Module1()
Dim RowNdx As Long
Dim ConfMonth As String
Dim DelPrevMs As String
Dim ConfirmDel As String
Application.DisplayAlerts = False
' ASK QUESTION
ConfirmDel = "Delete Data From The Prev. Months?"

' Display MessageBox
DelPrevMs = MsgBox(ConfirmDel, vbQuestion + vbYesNo, "Data From Prev. Months Elimination")
If DelPrevMs = vbYes Then

' PROMT ASKING THE DESIRED MONTH TO KEEP
ConfMonth = InputBox(Prompt:="Enter the Confirmed Month.", _
Title:="Confirmed Month", Default:="Type Month Here")
Else
MsgBox "Data FROM PREV. MONTHS WILL BE KEPT"
End If

' INSERT REPEATED COLUMN & FORMULA

Range("E2").Select

ActiveCell.FormulaR1C1 = _
"=IF(OR(AND(RC[-1]=R[1]C[-1],R[1]C[8]= ""ConfMonth""),AND(RC[-1]=R[-1]C[-1],RC[8]= ""ConfMonth"")),""True"",""X"")"

End Sub

=========================================
Unfortunatelly I get this in E2 in my Worksheet

=IF(OR(AND(D2=D3,M3= "ConfMonth"),AND(D2=D1,M2= "ConfMonth")),"True","X")

What I enter in the Prompt is May 10, so what I want to get in E2 is

=IF(OR(AND(D2=D3,M3= "May 10"),AND(D2=D1,M2= "May 10")),"True","X")

I figured that by making the changes below I get the following in E2:

=IF(OR(AND(D2=D3,M3= "May10"),AND(D2=D1,M2= "May10")),"True","X")

Nevertheless I really need that space in May 10
===================================================
CHANGES
===================================================
Dim ConfMonthx$


ActiveCell.FormulaR1C1 = _

"=IF(OR(AND(RC[-1]=R[1]C[-1],R[1]C[8]=" & ConfMonthx$ & "),AND(RC[-1]=R[-1]C[-1],RC[8]=" & ConfMonthx$ & ")),""True"",""X"")"
==========================================================


Can anyone help me with this problem? What should I do to make it work?

 

[Gonzalo De La Torre]

Thak you for your welcome,

An error appeared.

After trying what you mentioned:

ActiveCell.FormulaR1C1 = _
"=IF(OR(AND(RC[-1]=R[1]C[-1],R[1]C[8]= " & ConfMonth & "),AND(RC[-1]=R[-1]C[-1],RC[8]= " & ConfMonth & ")),""True"",""X"")"


The following error message appears:

Run-time error '1004':

Application-defined or Object-defined error

How can I declare my variable in this AND formula?

 

[Smitty]

Sorry, I deleted my original post because what I posted wouldn't work, so I went back to the drawing board.

This should do it:

ActiveCell.FormulaR1C1 = _
"=IF(OR(AND(RC[-1]=R[1]C[-1],R[1]C[8]= " & """" & ConfMonth & """" & "),AND(RC[-1]=R[-1]C[-1],RC[8]= " & """" & ConfMonth & """" & ")),""True"",""X"")"

HTH,

 

[Gonzalo De La Torre]

Thanx Smitty!

This is exactly what I needed!

I really appreciate it