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=553257

VBA Code For OR-Function Using Cell Property
 

"Quote marks used within ""Quoted Strings"" need to be ""Doubled up"""

"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=553257