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?
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?

Last edited: