RUNTIME ERROR 1004 ..定义 [英] RUNTIME ERROR 1004 .. DEFINE
问题描述
SUB显示()
设置fnd =范围(a:a)。查找(Br)
如果fnd为Nothing则退出Sub
设置第一个= fnd
设置prev = fnd
确定
设置fnd =范围(a:a) .Find(Br,fnd)
如果fnd Is Nothing或fnd.Address = first.Address然后退出Do
如果离开(fnd,3)=Br 那么
Debug.Print fnd.Address
rwcnt = fnd.row - prev.row
fnd.Offset(-1).Resize (10 - rwcnt).EntireRow.Insert
设置prev = fnd
结束如果
循环
END SUB
我的数据是
Br CHARUDQQWATTA SHRIRAEEM
DESHEEMUKH
PLOT NO。 12,TR1SARAN HOUSING
SOC布局,Gov'r附近。新闻
COLONY,JAASQWITALA RQWOAD,NAEQGPUR
44022016联系电话:071222QWQ4426990
·.09373107632
cSADsdesaahASDAukh @ hotmail .com
,~13014
Br DEEPSSAAK HA JAN BOKDE
SUBSAHASH ROAD,GANDHI附近>
SAGASAR,BESASDE K.RWAAO TAILORS
NAGPUASR 44001218
ContacLNo:72132409/736767
A2345
Br姓名CHANDRAKANT
ADD2
ADD1
ADD3
440024联络.No:094218359
EMAIL
如果收到错误消息但您不确定它的含义或原因,首先要做的是谷歌错误: Google运行时错误1004 vba [ ^ ]因为有一个非常非常好的机会,你不是第一个遇到问题的人。
在这种情况下,google有超过100,000个hiots,其中大部分都有建议和解决方案。
从那里开始:如果它们都不适合您的情况,至少在提出问题时您将获得更多信息。
如果第一行包含以Br
开头的数据,我只能重新创建您的问题。
如果您单步执行代码,您会发现找到的第一个Br实例始终是第2行或更高版本。但是MSDN说:当搜索到达指定搜索范围的末尾时,它会回绕到范围的开头。要在发生此环绕时停止搜索,请保存第一个找到的单元格的地址,然后针对此保存的地址测试每个连续的找到单元格地址。
由于该环绕变量
rwcnt
以负值结束......砰!你的错误被提出了。
你试图用解决这个问题如果fnd Is Nothing或fnd.Address = first.Address然后退出Do
但是查找
包围fnd.Address
实际上之前first.Address
有几种方法你可以解决这个问题。您可以确保数据的第一行与Br不匹配 - 例如通过添加标题或空白行。
或者,将测试更改为如果fnd为Nothing或fnd.Row< = first.Row然后退出Do
样本数据显示为行fnd.Offset(-1).Resize(10 - rwcnt).EntireRow.Insert仍然会产生错误,如果
rwcnt
是任何值> 9作为参数然后传递给调整大小
为负数或零(非法值)。
这不完全清楚你要用这行代码做什么 - 如果你试图在开始Br的每一行之前插入一组行,那么你需要重新审视你如何计算要插入的行数的逻辑。 / BLOCKQUOTE>
SUB EXPLICIT()
Set fnd = Range("a:a").Find("Br")
If fnd Is Nothing Then Exit Sub
Set first = fnd
Set prev = fnd
Do
Set fnd = Range("a:a").Find("Br", fnd)
If fnd Is Nothing Or fnd.Address = first.Address Then Exit Do
if left(fnd,3) = "Br " then
Debug.Print fnd.Address
rwcnt = fnd.row - prev.row
fnd.Offset(-1).Resize(10 - rwcnt).EntireRow.Insert
set prev = fnd
end if
Loop
END SUB
MY DATA ARE
Br CHARUDQQWATTA SHRIRAEEM
DESHEEMUKH
PLOT NO. 12, TR1SARAN HOUSING
SOC LAYOUT, NEAR Gov'r. PRESS
COLONY, JAASQWITALA RQWOAD, NAEQGPUR
44022016 Contact No:071222QWQ4426990
· .09373107632
cSADsdesaahASDAukh@hotmail.com
,~13014
Br DEEPSSAAK HA JAN BOKDE
SUBSAHASH ROAD, NEAR GANDHI
SAGASAR, BESASDE K.RWAAO TAILORS
NAGPUASR 44001218
ContacLNo:72132409/736767
A2345
Br NAME CHANDRAKANT
ADD2
ADD1
ADD3
440024 Contact.No: 094218359解决方案If you get an error message and you aren't sure what it means or why it's there, the first thing to do is google the error: Google "runtime error 1004 vba"[^] as there is a very, very good chance that you aren;t teh first person to meet the problem.
In this case, google has over 100,000 hiots, most of which have suggestions and solution.
Start there: and if none of them fit your situation, at least you will have beeter information when you ask a question.
I can only recreate your problem if the first row contains data starting with"Br "
.
If you step through your code you will notice that the first instance of "Br" found is always row 2 or later. HoweverMSDN says:When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
As a result of that wrap-around the variable
rwcnt
ends up with a negative value and ... Bang! Your error is raised.
You've attempted to address this withIf fnd Is Nothing Or fnd.Address = first.Address Then Exit Do
But as theFind
wraps around thefnd.Address
is actually before thefirst.Address
There are a couple of ways you can address the problem. You could ensure that the first row of your data does not match "Br " - e.g. by adding a header or blank row.
Alternatively, change the test toIf fnd Is Nothing Or fnd.Row <= first.Row Then Exit Do
[EDIT]
With the sample data shown the linefnd.Offset(-1).Resize(10 - rwcnt).EntireRow.Insertwill still produce the error if
rwcnt
is any value > 9 as the parameter then passed toResize
is negative or zero (an illegal value).
It's not entirely clear what you are trying to do with that line of code - if you are trying to insert a set of rows before each row that begins "Br " then you need to revisit your logic on how to calculate the number of rows to insert.
这篇关于RUNTIME ERROR 1004 ..定义的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!