how to copy one column to infinite column by 5 second time interval using macro

[Niranjanmeyda]

The A1 Column value in my sheet change randomly so i want to copy A1 in every 5 second and paste A1 value to infinite column

 

[joeu2004]

I presume you mean: copy A1 to the end of another column every 5 seconds.

Perhaps something like the following.

Note: I use a 2-second frequency for demonstration purposes. I entered the formula =RAND() into A1. I formatted column B as General and column C as Custom "m/dd/yyyy hh:mm:ss" without quotes.

In VBA, enter the following into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' must stop any recurring event before close.
' otherwise, workbook will be reopened automatically
' when scheduled event occurs, unless we also close
' this instance of Excel
stopit
End Sub

Enter the following into a normal module. Customize the lines highlighted in red.

Public ev As Double
Private resched As Boolean


' in Excel, press alt-f8 to execute "startit"
' to start recurring event

Sub startit()
resched = True
copyit
End Sub


' do not execute directly

Sub copyit()
Dim n As Double, r As Long

' just in case we could not stop recurring event
' because VBA was reset (see comments in "stopit")
If Not resched Then Exit Sub

' capture current time and reschedule immediately
' to minimize "time skew"
'
' caveat: first update might occur up to 1 second
' sooner because VBA Now is rounded down to the second
n = Now
ev = n + TimeSerial(0, 0, 2)
Application.OnTime ev, "copyit"

' update target cells
If IsEmpty(Range("b1")) Then r = 1 _
Else r = Cells(Rows.Count, "b").End(xlUp).Row + 1
Range("a1").Calculate
Cells(r, "b") = Range("a1")  ' copy A1 to end of column B
Cells(r, "c") = n            ' enter current time into column C
Columns("b:c").AutoFit
End Sub


' in Excel, press alt-f8 to execute "stopit"
' to stop recurring event

Sub stopit()
' caveat: if VBA is reset, ev will become zero,
' and we cannot prevent scheduled update.
' this also prevents late update due to race
' condition (below)
resched = False
If ev <> 0 Then
    ' race condition: timeout for scheduled update
    ' might occur at the same time
    On Error Resume Next
    Application.OnTime ev, "copyit", , False
End If
End Sub

 

[joeu2004]

Range("a1").Calculate

I got away with using Calculate because my formula in A1 is "volatile" ( =RAND() ). But in general, we should use Dirty when Automatic calculation mode is enabled.

It might not make any difference to niranjanmeyda, depending on what is meant by ``value in my sheet change randomly``.

 

[Niranjanmeyda]

I presume you mean: copy A1 to the end of another column every 5 seconds.

Perhaps something like the following.

Note: I use a 2-second frequency for demonstration purposes. I entered the formula =RAND() into A1. I formatted column B as General and column C as Custom "m/dd/yyyy hh:mm:ss" without quotes.

In VBA, enter the following into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' must stop any recurring event before close.
' otherwise, workbook will be reopened automatically
' when scheduled event occurs, unless we also close
' this instance of Excel
stopit
End Sub

Enter the following into a normal module. Customize the lines highlighted in red.

Public ev As Double
Private resched As Boolean


' in Excel, press alt-f8 to execute "startit"
' to start recurring event

Sub startit()
resched = True
copyit
End Sub


' do not execute directly

Sub copyit()
Dim n As Double, r As Long

' just in case we could not stop recurring event
' because VBA was reset (see comments in "stopit")
If Not resched Then Exit Sub

' capture current time and reschedule immediately
' to minimize "time skew"
'
' caveat: first update might occur up to 1 second
' sooner because VBA Now is rounded down to the second
n = Now
ev = n + TimeSerial(0, 0, 2)
Application.OnTime ev, "copyit"

' update target cells
If IsEmpty(Range("b1")) Then r = 1 _
Else r = Cells(Rows.Count, "b").End(xlUp).Row + 1
Range("a1").Calculate
Cells(r, "b") = Range("a1")  ' copy A1 to end of column B
Cells(r, "c") = n            ' enter current time into column C
Columns("b:c").AutoFit
End Sub


' in Excel, press alt-f8 to execute "stopit"
' to stop recurring event

Sub stopit()
' caveat: if VBA is reset, ev will become zero,
' and we cannot prevent scheduled update.
' this also prevents late update due to race
' condition (below)
resched = False
If ev <> 0 Then
    ' race condition: timeout for scheduled update
    ' might occur at the same time
    On Error Resume Next
    Application.OnTime ev, "copyit", , False
End If
End Sub

how can we copy A1 to infinite column using for loop.

 

[joeu2004]

how can we copy A1 to infinite column using for loop.

I believe that is what I provided.

In your original posting __here__, you said that A1 __does__ change "randomly" (i.e. periodically), so you want to copy A1 every 5 seconds and paste to an "infinite column" (i.e the end of a column).

The recurring OnTime event that I provided does exactly that. "Recurring" = "loop" of a sort.

("Infinite"? Well, up to 60 days 16 hours 21 minutes 20 seconds -- 1,048,576 times at 5-second intervals.)

Did you try the code that I provided?!

If it does not do what you intended, explain how the current behavior differs from what you need.

And provide a concrete example that describes in detail what values are in A1 over time, what causes A1 to change, and what you expect to see in another column over time.

-----

PS.... I just realized that I interpreted "copy and paste" as simply "store" the value from A1 into another column.

If you literally want to copy and paste (value), replace the statement

Cells(r, "b") = Range("a1")

with the statement

Range("a1").Copy
Cells(r, "b").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

 

[joeu2004]

The A1 Column value in my sheet change randomly so i want to copy A1 in every 5 second and paste A1 value to infinite column

I wonder if you mean: A1 changes every 5 second; and whenever it changes, you want to copy A1 and paste to the end of another column?

And I just noticed that you wrote "A1 column value", not just A1. What does "A1 column value" mean? A1 is a cell name. Do you mean "any value in column A"?!

Bottom line: I'm afraid that your description of the problem is not clear enough for me to proceed. Again, I would need a concrete demonstration of what changes when, and why or how, together with what action you want to happen when that change occrurs.

Otherwise, I am unable to help you further. I worry that my misunderstandings will result in misdirection by my answers.

Hopefully, someone else will have a clearer understanding and step up to help. Good luck!

 

[Niranjanmeyda]

I wonder if you mean: A1 changes every 5 second; and whenever it changes, you want to copy A1 and paste to the end of another column?

And I just noticed that you wrote "A1 column value", not just A1. What does "A1 column value" mean? A1 is a cell name. Do you mean "any value in column A"?!

Bottom line: I'm afraid that your description of the problem is not clear enough for me to proceed. Again, I would need a concrete demonstration of what changes when, and why or how, together with what action you want to happen when that change occrurs.

Otherwise, I am unable to help you further. I worry that my misunderstandings will result in misdirection by my answers.

Hopefully, someone else will have a clearer understanding and step up to help. Good luck!

CAN YOU DO THIS WITH FOR LOOP OR FOREACH