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
[e-mail]

,~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
EMAIL

解决方案

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. However

MSDN 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 with

If fnd Is Nothing Or fnd.Address = first.Address Then Exit Do

But as the Find wraps around the fnd.Address is actually before the first.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 to

If fnd Is Nothing Or fnd.Row <= first.Row Then Exit Do

[EDIT]
With the sample data shown the line

fnd.Offset(-1).Resize(10 - rwcnt).EntireRow.Insert

will still produce the error if rwcnt is any value > 9 as the parameter then passed to Resize 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屋！