RUNTIME ERROR 1004 .. DEFINE

Question

0.00/5 (No votes)

See more:

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
EMAIL

Posted 10-Dec-15 20:46pm

Member 12200308

Updated 11-Dec-15 0:50am

v2

Add a Solution

Comments

Richard MacCutchan 11-Dec-15 4:22am

What? Where?

Member 12200308 11-Dec-15 4:26am

sir , if i was run this program ,i got runtime error 1004.. plz help me

Richard MacCutchan 11-Dec-15 4:41am

Where does it occur, and what is the complete text of the message?

Member 12200308 11-Dec-15 4:42am

sub Explicit()
that was correct or not??

Richard MacCutchan 11-Dec-15 4:48am

Run the code, see where the error occurs, capture the full text of the message, and please show us the details. We cannot guess what may be happening.

Member 12200308 11-Dec-15 5:03am

if i run this program then i got run time error 1004 , application defined or object defined error

Member 12200308 11-Dec-15 5:35am

Private Sub CommandButton1_Click()
Set fnd = Range("a:a").Find("br")
If fnd Is Nothing Then Exit Sub
Set first = fnd
Do
Set prev = fnd
Set fnd = Range("a:a").Find("br", fnd)
If fnd Is Nothing Or fnd.Address = first.Address Then Exit Do
Debug.Print fnd.Address
rwcnt = fnd.Row - prev.Row
fnd.Offset(-1).Resize(10 - rwcnt).EntireRow.Insert
Loop
End Sub

I GOT ERROR ON THIS " fnd.Offset(-1).Resize(10 - rwcnt).EntireRow.Insert"

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-12-11T00:04:00

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.

OriginalGriff · Answer 2 · 2015-12-10T22:29:00

Solution 1

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.

Posted 10-Dec-15 22:29pm

OriginalGriff

Comments

Member 12200308 11-Dec-15 4:35am

sir , i was check this in google, but no one help me.. sir i try all thing , there fore i try this site..

OriginalGriff 11-Dec-15 4:45am

So what of those suggestions did you check and discount?

Member 12200308 11-Dec-15 4:50am

sir i was use sub expilicit(), that was correct or not , i am confuse