vba函数（按钮）将单元格的值更改几毫秒，然后还原 [英] vba function(button) to change a cell's value for a few milliseconds then revert

问题描述

公开声明Sub Sleep Libkernel32（ByVal dwMilliseconds As Long）
sub changeto1quickly（）
range（C1）。Value = 1
sleep（1）
（C1）。Value = 0
End sub

$ b $上面的b

可以将C1更改为1，然后暂停它然后将其还原为0，所以现在我需要将其汇总到一个列，其中偏移量包含引用

我需要能够将单元格偏移量的值更改为包含某个单词的列左侧。例如在COLUMNS C 和 D 中，以便B列中的Dim我需要运行上述子快速将零修改为零。

  BDE 
 1 dim 0 
 dim 0 
 car 0 
 car 0 
 dim 0 
 car 0 
  

我需要能够做一个VBA公式将几乎做任何excel如果公式会做，如果你把它拖下来。我在这里找到： http://www.quepublishing.com/文章/ article.aspx？p = 2021718& seqNum = 8
假设您有一列列A中的产品，其列表中的总数为总计。如果您想查找任何总数等于零并将LOW置于旁边的单元格中，执行此操作

：

 设置Rng =范围（ B1：B16）Find（What：=0，LookAt：= xlWhole，LookIn：= xlValues）
 Rng.Offset（，1）.Value =LOW
  

尽管我需要的略有不同，不是指A或B列从A到不相邻的列。要检查是D：D有Dim，然后将1放在C列中的任何单元格中：C offset to coumn D：D
肯定这可以根据需要进行调整。可能..

作为子

 公开声明Sub Sleep Lib kernel32（ByVal dwMilliseconds As Long）
 sub pump_onall（）
 
设置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 
结束sub 
  

我在设置的Rng线上收到错误

  Sub pump_onall（）
 
设置Rng =表（帐户详细信息--->）范围（DH1：DH50） .Value.Find（什么：=DQ3，LookAt：= xlWhole，LookIn：= xlValues）
 Rng.Offset（0，-7）.Value = 1 
睡眠（1）
 Rng.Offset（0，-7）.Value = 0 
 End Sub 
  

这可以工作

  Sub pump_onall（）
 
表格（帐户详细信息---> ）.Range（DH1：DH50）。Value.Find（什么：=DQ3，LookAt：= xlWhole，LookIn：= xlValues）
表格（帐户详细信息--->） （DH1：DH50）Offset（0，-7）.Value = 1 
 Sleep（DH1：DH50）。Offset（0，-7）.Value = 0 
 End Sub 
  

我得到的错误是9个下标超出范围

解决方案

Sleep 功能将允许您执行此操作：

 公共声明Sub Sleep Libkernel32（ByVal dwMilliseconds As Long）
 Sub SleepTest（）
 Debug.Print Timer'do something 
睡眠（100）'等待第10秒
调试打印计时器'做别的东西
 End Sub 
  

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

解决方案

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

这篇关于vba函数（按钮）将单元格的值更改几毫秒，然后还原的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！