Is it anything wrong with this code?

daniboymu

New Member
Joined
Nov 1, 2020
Messages
44
Office Version
  1. 2019
Platform
  1. Windows
so I have a code for a macro and everytime I run, my excel freezes, Idk what to do anymore, please someone could help me

CODE:
VBA Code:
Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        Application.Wait (Now + TimeValue("0:00:05"))
    Loop
   
End Sub
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

daniboymu

New Member
Joined
Nov 1, 2020
Messages
44
Office Version
  1. 2019
Platform
  1. Windows
I need to use the sheet while this macro is running, how could I do that?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,709
DoEvents lets you use Excel, though I don't think it'll let you use it while Wait is running.

Code works fine here, but since it's constantly running it may look frozen.

VBA Code:
Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        DoEvents
        Application.Wait (Now + TimeValue("0:00:05"))
    Loop
  
End Sub
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,709
The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue.

 

daniboymu

New Member
Joined
Nov 1, 2020
Messages
44
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

DoEvents lets you use Excel, though I don't think it'll let you use it while Wait is running.

Code works fine here, but since it's constantly running it may look frozen.

VBA Code:
Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        DoEvents
        Application.Wait (Now + TimeValue("0:00:05"))
    Loop
 
End Sub
Could you please help me, because I need to keep use the sheet while the macro is running. there is something I can do about it?
 

daniboymu

New Member
Joined
Nov 1, 2020
Messages
44
Office Version
  1. 2019
Platform
  1. Windows
because all I want is the macro running on a especific time and also running the macro I can use the sheet
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,709

ADVERTISEMENT

Try this:

VBA Code:
Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        WasteTime
    Loop
   
End Sub

Sub WasteTime()
Dim time1, time2

time1 = Now
time2 = Now + TimeValue("0:00:05")

    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

End Sub

 

daniboymu

New Member
Joined
Nov 1, 2020
Messages
44
Office Version
  1. 2019
Platform
  1. Windows
Try this:

VBA Code:
Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        WasteTime
    Loop
 
End Sub

Sub WasteTime()
Dim time1, time2

time1 = Now
time2 = Now + TimeValue("0:00:05")

    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

End Sub

this is exactly what I want there is only one thing left, i'm very sorry to bother you too much. I need that the macro only runs on a especific spreedsheet, because i have 3 spreedsheets, one with the data, other with the calculations and the last one with the chart, I only Need the macro running on the database sheet, is it possible to do that?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,709
Not a worry

VBA Code:
Sub Atualizar()

Dim ws As Worksheet

Set ws = Sheets("database")

    Do
       If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        ws.Rows(7).Insert
        ws.Range("A8:DG8").Value = ws.Range("A5:DG5").Value
        WasteTime
    Loop
   
End Sub

Sub WasteTime()

Dim bla As String

bla = Range("A1").Value

Sheets("Setup Page").Range("Z26:Z30").AutoFilter Field:=1, Criteria1:="*" & bla & "*"

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,273
Members
416,086
Latest member
CaptainGD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top