Use Variable in AND formula in excel

Gonzalo De La Torre

New Member
Joined
May 21, 2010
Messages
24
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? :confused:
 
Last edited:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Gonzalo De La Torre

New Member
Joined
May 21, 2010
Messages
24
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

Legend
Joined
May 15, 2003
Messages
29,536
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,290
Messages
5,600,759
Members
414,405
Latest member
Zaurb

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