Excel VBA first timer

[Ashrum145]

I'm trying to ge the following code to run on a change in Sheet2 but the thing does not work.

Sub ScheduleCheck()
RowNumber = 2
Do While Not (IsEmpty(Sheet2.Cells(RowNumber, 3)))
SchedRow = 2
Do While Not (IsEmpty(Sheet1.Cells(SchedRow, 2)))
If Sheet2.Cells(RowNumber, 3) = Sheet1.Cells(SchedRow, 2) Then
Sheet1.Cells(SchedRow, 3) = Sheet2.Cells(RowNumber, 4)
Sheet1.Cells(SchedRow, 4) = Sheet2.Cells(RowNumber, 5)
End If
SchedRow = SchedRow + 1
Loop
RowNumber = RowNumber + 1
Loop
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Sheet2.Range("D2:E8")) Then
ScheduleCheck
End If
End Sub

I know ScheduleCheck() runs the way I want it to by running it through the VBA environment and it only does not work with the Change Sub.
I want to run ScheduleCheck when there is a change in Sheet2.Range("D2:E8") so if Target is in that range Intersect() = True... right? Right now if there is a change in the Range() nothing is happening to Sheet1 (which is what ScheduleCheck() does).

Thanks.

 

[SkipVought]

hi,

try this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If NOT Intersect(Target, Sheet2.Range("D2:E8")) Is Nothing  Then
        ScheduleCheck
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ashrum145]

No change, if I run the program manually it works but will still not run on a change
Why would the double negatives work different than without?

 

[SkipVought]

In the event that there was NO intersect.

Try fully specifying the VALUE property...

            If Sheet2.Cells(RowNumber, 3)[b].Value[/b] = Sheet1.Cells(SchedRow, 2)[b].Value[/b] Then
                Sheet1.Cells(SchedRow, 3)[b].Value[/b] = Sheet2.Cells(RowNumber, 4)[b].Value[/b]
                Sheet1.Cells(SchedRow, 4)[b].Value[/b] = Sheet2.Cells(RowNumber, 5)[b].Value[/b]
            End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ashrum145]

Still no love.

 

[SkipVought]

Put a break in your code.

Change a value on sheet2 and STEP thru your code, observing the VALUES in your code.

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Where is the sub:
Private Sub Worksheet_Change
physically located?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Ashrum145]

xlbo the code is as pasted in the Workbook section of code. I tried to put the Workbook_Change code in the Sheet2 code section but I could not get it to call ScheduleCheck from there.

 

[SkipVought]

The Worksheet_Change event code belongs in the WORKSHEET OBJECT code window (of the sheet of interest), NOT the WORKBOOK Object code window!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ashrum145]

SkipVought I did change the code to the WORKSHEET object area for sheet2 and now I am getting a compile error telling me that ScheduleCheck() is not defined.
ScheduleCheck() is in the Workbook code because I want multiple sheets to run it in the end so do I have to do something in sheet2 code (where the change will happen) to declair that the code exists?

 

[SkipVought]

ScheduleCheck() is in the Workbook code

Wrong! It must be in a MODULE in order to be called as posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}