Patrickwest

09-27-2016, 06:29 PM

Hi to everyone.

Currently I am using the following programme in workbook1

Dim Seconds As long

Dim Minutes As long

Dim CurrentRow As long

Dim CurrentColumn As long

Dim Initalised As Boolean

Private Sub Recalc()

If (CurrentRow <> 0 And CurrentColumn <> 0) Then

Cells(CurrentRow, CurrentColumn).Value = Format(Now, "dd/mm/yy")

Cells(CurrentRow, CurrentColumn+1).Value = Format(Time, "hh:mm:ss AM/PM")

Seconds = Seconds + 1

If (Seconds = 60) Then

Minutes = Minutes + 1

CurrentRow = CurrentRow + 1

Seconds = 0

End If

' Stop repeating after 24 hours (1440 minutes)

If (Minutes < 1440) Then

Call SetTime

Else

Initalised = False

End If

End If

End Sub

Sub SetTime()

If (Not Initalised) Then

Initalised = True

' Initialise variables.

Seconds = 0

Minutes = 0

CurrentRow = ActiveCell.Row

CurrentColumn = ActiveCell.Column

End If

SchedRecalc = Now + TimeValue("00:00:01")

Application.OnTime SchedRecalc, "Recalc"

End Sub

The result of the above will be shown in Cells A1 and B1 all the way to Cells A1440 and B1440.

Cell C1 will have for example as 00:00:00 (Time). Cell C2 will be 00:01:00 and so on until Cell C1440.

Cell D1 will have formula IF(B1=C1,1,"") and continue all the way to Cell D1440.

Cell E1 will have the formula VALUE(D1) and continue all the way to Cell E1440.

Workbook 2 has the following programme.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 1 Then Exit Sub

Cells(Target.Row, 3) = Evaluate("=IF(B" & Target.Row & "=1,RANDBETWEEN(0,9),"""")")

End Sub

In workbook2 Cell A1 will have cut & paste with link from workbook1 Cell E1. This continue till Cell A1440.

Cell B1 in workbook2 will have a formula IF(A1=1,1,""). This will continue till Cell B1440.

Cell C1 in workbook2 suppose to show the result of VBA programme that is IF(B1=1,RANDBETWEEN(0,9),"")

When Cell E1 in workbook shows no.1, it will be automatically reflected in Cell A1 of workbook2 because of the link.

The problem is, I am not getting the RANDBETWEEN(0,9) in Cell C1 of workbook 2.

Even if I key in no.1 manually in Cell E1 in workbook1, I am not getting the result in Cell C1 of the workbook2.

When I key in no.1 in Cell A1 in workbook 2 manually, only then I will get the result in Cell C1 of the workbook2.

Can someone solve this problem for me?

I could not run two different macros properly when I run in the same workbook but in different sheets. Even if I open a new page within the same workbook, the clock is being affected.

Therefore, I have to run this programme in TWO different workbooks. The clock has to be in a workbook on it's own without being affected.

Thinking in advance.

Currently I am using the following programme in workbook1

Dim Seconds As long

Dim Minutes As long

Dim CurrentRow As long

Dim CurrentColumn As long

Dim Initalised As Boolean

Private Sub Recalc()

If (CurrentRow <> 0 And CurrentColumn <> 0) Then

Cells(CurrentRow, CurrentColumn).Value = Format(Now, "dd/mm/yy")

Cells(CurrentRow, CurrentColumn+1).Value = Format(Time, "hh:mm:ss AM/PM")

Seconds = Seconds + 1

If (Seconds = 60) Then

Minutes = Minutes + 1

CurrentRow = CurrentRow + 1

Seconds = 0

End If

' Stop repeating after 24 hours (1440 minutes)

If (Minutes < 1440) Then

Call SetTime

Else

Initalised = False

End If

End If

End Sub

Sub SetTime()

If (Not Initalised) Then

Initalised = True

' Initialise variables.

Seconds = 0

Minutes = 0

CurrentRow = ActiveCell.Row

CurrentColumn = ActiveCell.Column

End If

SchedRecalc = Now + TimeValue("00:00:01")

Application.OnTime SchedRecalc, "Recalc"

End Sub

The result of the above will be shown in Cells A1 and B1 all the way to Cells A1440 and B1440.

Cell C1 will have for example as 00:00:00 (Time). Cell C2 will be 00:01:00 and so on until Cell C1440.

Cell D1 will have formula IF(B1=C1,1,"") and continue all the way to Cell D1440.

Cell E1 will have the formula VALUE(D1) and continue all the way to Cell E1440.

Workbook 2 has the following programme.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 1 Then Exit Sub

Cells(Target.Row, 3) = Evaluate("=IF(B" & Target.Row & "=1,RANDBETWEEN(0,9),"""")")

End Sub

In workbook2 Cell A1 will have cut & paste with link from workbook1 Cell E1. This continue till Cell A1440.

Cell B1 in workbook2 will have a formula IF(A1=1,1,""). This will continue till Cell B1440.

Cell C1 in workbook2 suppose to show the result of VBA programme that is IF(B1=1,RANDBETWEEN(0,9),"")

When Cell E1 in workbook shows no.1, it will be automatically reflected in Cell A1 of workbook2 because of the link.

The problem is, I am not getting the RANDBETWEEN(0,9) in Cell C1 of workbook 2.

Even if I key in no.1 manually in Cell E1 in workbook1, I am not getting the result in Cell C1 of the workbook2.

When I key in no.1 in Cell A1 in workbook 2 manually, only then I will get the result in Cell C1 of the workbook2.

Can someone solve this problem for me?

I could not run two different macros properly when I run in the same workbook but in different sheets. Even if I open a new page within the same workbook, the clock is being affected.

Therefore, I have to run this programme in TWO different workbooks. The clock has to be in a workbook on it's own without being affected.

Thinking in advance.