vba function(button) to change a cell's value for a few milliseconds then revert

Question

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
sub changeto1quickly()
range("C1").Value = 1
sleep(1) 
("C1").Value= 0 
End sub

above works to change C1 to 1 then pause it then revert it to 0 so now I need to aggregate this across a column where the offset contains a reference

I need to be able to change the value of the cells offset to the left of a column containing a certain word. For example in COLUMNS C and D so that every cel in column B that has Dim I need to run the above sub to quickly changes the zeros to ones.

    B    D    E
  1 dim   0
    dim   0
    car   0
    car   0
    dim   0
    car   0

I need to be able to make a VBA formula that would do pretty much what any excel if formula would do if you dragged it down. I found this here: http://www.quepublishing.com/articles/article.aspx?p=2021718&seqNum=8 Suppose you have a list of produce in column A with totals next to them in column B. If you want to find any total equal to zero and place LOW in the cell next to it, do this:

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole,        LookIn:=xlValues)
Rng.Offset(, 1).Value = "LOW"

Although I'd need it set out slightly differently not referring to column A or B from A but to a non adjacent column . I.e to check is D:D has Dim then put 1 in any cell that does in column C:C offset to coumn D:D surely this can be adjusted for what I need. Maybe..

as a sub

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
sub pump_onall()

 Set Rng = Range("B1:B16").Find(What:="Dim", LookAt:=xlWhole,          LookIn:=xlValues)
Rng.Offset(3, 0).Value = 1
sleep(1)
Rng.Offset(3,0).Value = 0
End sub

I get the error on the set Rng line

Sub pump_onall()

Set Rng = Sheets("Account Details    --->").Range("DH1:DH50").Value.Find(What:="DQ3", LookAt:=xlWhole,   LookIn:=xlValues)
Rng.Offset(0, -7).Value = 1
Sleep (1)
Rng.Offset(0, -7).Value = 0
End Sub

Surely this can work

Sub pump_onall()

Sheets("Account Details --->").Range("DH1:DH50").Value.Find(What:="DQ3",  LookAt:=xlWhole, LookIn:=xlValues)
Sheets("Account Details --->").Range("DH1:DH50").Offset(0, -7).Value = 1
Sleep (1)
Sheets("Account Details --->").Range("DH1:DH50").Offset(0, -7).Value = 0
End Sub

the error I get is error 9 subscript out of range

That's good intention. What have you tried so far? Where are you stuck? Or you just came here to get a ready solution? — ttaaoossuuuu, Mar 06 '15 at 10:46
Set your cell value to 1, then Sleep(1), then set to zero again? — laylarenee, Mar 06 '15 at 10:59
thanks laylarenee this worked - the question has been updated and tidied up - maybe I will delete this and post it again — Jon white, Mar 06 '15 at 15:09

score 0 · Answer 1 · answered Mar 06 '15 at 11:08

0

The Sleep function will allow you to do this:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SleepTest()
    Debug.Print Timer ' do something
    Sleep (100)       ' wait for 10th second
    Debug.Print Timer ' do something else
End Sub

answered Mar 06 '15 at 11:08

laylarenee

3,276
7
32
40

There is no need to delete this thread, especially if it helped solve your problem. – laylarenee Mar 06 '15 at 11:24
Questions should be concise. I would suggest to search the internet first. If you can't find instructions, then ask a new question. – laylarenee Mar 06 '15 at 12:38
Since this question is a duplicate now, it will not get much (if any) attention. I'm honestly not sure what your trying to do either, could you ask a new question with a more basic scope. For starters, you are changing these values to 1 for a purpose... what is that purpose specifically? and why does it have to be a short period of time? Are you changing this to one to perform a summation? – laylarenee Mar 06 '15 at 14:19
I have got really close to doing it actually , see bottom of question. – Jon white Mar 06 '15 at 14:42

vba function(button) to change a cell's value for a few milliseconds then revert

1 Answers1

Linked