VBA Code For OR-Function Using Cell Property
 

:confused:
Here is the OR-FUNCTION Formula which works but I need to change
formula to use cell property references instead.

If Worksheets("Project Info").Range("$J2").Value = 1 Then
Worksheets("Update Schedule").Range("$B2").Value =
"=OR((YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=0" _
& "YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=12)"


HERE'S THE CODE FOR CHANGING TO USE CELL PROPERTY

I count number of rows in worksheet to find out how may times I must
loop through the formulas below in section FORMULAS FOR UPDATE
SCHEDULE. This works o'kay.

'COUNTING NUMBER OF CLIENTS ON PROJECT INFO WORKSHEET
Worksheets("Project Info").Activate
Dim r As Integer
Dim row As Range

With ActiveSheet
r = Worksheets("Project Info").Range("S1").Value + 1
Set row = Range(Cells(2, 1), Cells(r, 13))
Range(Cells(2, 1), Cells(r, 13)).Select
Worksheets("Formulas").Range("A2").Value = Selection.Rows.Count
Range("A1").Select
End With

'FORMULAS FOR UPDATE SCHEDULE
Worksheets("Update Schedule").Activate

Dim a As Integer
Dim sr As Integer
Dim z As Integer

'start row variable
sr = Worksheets("Formulas").Range("B2").Value
'per year variable
a = sr
'loop variable
z = Worksheets("Formulas").Range("A2").Value + 1

'Formula
For numclient = 1 To z
If Worksheets("Project Info").Cells(a, 10) = 12 Then
Worksheets("Update Schedule").Cells(a, 2) = "TRUE"
ElseIf Worksheets("Project Info").Cells(a, 10) = 1 Then
Worksheets("Update Schedule").Cells(a, 2) =
"=OR((YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12"
_
& "+((MONTH(Cells(1,2))-MONTH(Worksheets("Project
Info").Cells(a,1)))=0," _
& "YEAR(Cells(1,2))-YEAR(Worksheets("Project
Info").Cells(a,1))*12" _
& "+((Cells(1,2)-MONTH('Worksheets("Project
Info").Cells(a,1)))=12)"
End If
a = a + 1
Next numclient

My problem is with the ElseIf statement using the OR-Function. I
receive a Compile error: Expected: end of statement at this point
...-YEAR(Worksheets("Project Info").Cells(a,1))... The compiler stops
at "Project

Info". I'm having a mental block on how to fix this.

Is there anyone who can help me. Thanks. Kylie


--
kylie
------------------------------------------------------------------------
kylie's Profile: http://www.excelforum.com/member.php...o&userid=35565
View this thread: http://www.excelforum.com/showthread...hreadid=553255

VBA Code For OR-Function Using Cell Property
 

I'm not sure if this will do what you want, but you'll one version of how to
write those formulas.

Sometimes, it'll be easier to switch to R1C1 reference style
(tools|options|general tab) to see how the formula works.

Option Explicit
Sub testme()
Dim a As Long
Dim myStr As String

a = 12

myStr = "$$$OR(YEAR(r2c2)-YEAR('Project Info'!r" & a & "c1)*12" _
& "+((MONTH(r1c2))-MONTH('project info'!r" & a & "c1))=0," _
& "YEAR(r1c2)-YEAR('project info'!r" & a & "c1))*12" _
& "+((r1c2)-MONTH('project info'!r" & a & "c1))=12"

Worksheets("Update Schedule").Cells(a, 2).FormulaR1C1 = myStr
End Sub

kylie wrote:

:confused:
Here is the OR-FUNCTION Formula which works but I need to change
formula to use cell property references instead.

If Worksheets("Project Info").Range("$J2").Value = 1 Then
Worksheets("Update Schedule").Range("$B2").Value =
"=OR((YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=0" _
& "YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=12)"

HERE'S THE CODE FOR CHANGING TO USE CELL PROPERTY

I count number of rows in worksheet to find out how may times I must
loop through the formulas below in section FORMULAS FOR UPDATE
SCHEDULE. This works o'kay.

'COUNTING NUMBER OF CLIENTS ON PROJECT INFO WORKSHEET
Worksheets("Project Info").Activate
Dim r As Integer
Dim row As Range

With ActiveSheet
r = Worksheets("Project Info").Range("S1").Value + 1
Set row = Range(Cells(2, 1), Cells(r, 13))
Range(Cells(2, 1), Cells(r, 13)).Select
Worksheets("Formulas").Range("A2").Value = Selection.Rows.Count
Range("A1").Select
End With

'FORMULAS FOR UPDATE SCHEDULE
Worksheets("Update Schedule").Activate

Dim a As Integer
Dim sr As Integer
Dim z As Integer

'start row variable
sr = Worksheets("Formulas").Range("B2").Value
'per year variable
a = sr
'loop variable
z = Worksheets("Formulas").Range("A2").Value + 1

'Formula
For numclient = 1 To z
If Worksheets("Project Info").Cells(a, 10) = 12 Then
Worksheets("Update Schedule").Cells(a, 2) = "TRUE"
ElseIf Worksheets("Project Info").Cells(a, 10) = 1 Then
Worksheets("Update Schedule").Cells(a, 2) =
"=OR((YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12"
_
& "+((MONTH(Cells(1,2))-MONTH(Worksheets("Project
Info").Cells(a,1)))=0," _
& "YEAR(Cells(1,2))-YEAR(Worksheets("Project
Info").Cells(a,1))*12" _
& "+((Cells(1,2)-MONTH('Worksheets("Project
Info").Cells(a,1)))=12)"
End If
a = a + 1
Next numclient

My problem is with the ElseIf statement using the OR-Function. I
receive a Compile error: Expected: end of statement at this point
..-YEAR(Worksheets("Project Info").Cells(a,1))... The compiler stops
at "Project

Info". I'm having a mental block on how to fix this.

Is there anyone who can help me. Thanks. Kylie

--
kylie
------------------------------------------------------------------------
kylie's Profile: http://www.excelforum.com/member.php...o&userid=35565
View this thread: http://www.excelforum.com/showthread...hreadid=553255

--

Dave Peterson