VBA Code For OR-Function Using Cell Property

[kylie]

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

 

[Dave Peterson]

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