Click here to Skip to main content
14,699,489 members
Please Sign up or sign in to vote.
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
Updated 11-Dec-15 1:50am
v2
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"


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.
   
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
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.
   
v2
Comments
Member 12200308 11-Dec-15 6:33am
   
<pre lang="VB.NET">Sub brinsert()
Dim fnd As Range, first As Range, prev As Range, rwcnt As Long
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.Row <= first.Row Then Exit Do
If Left(fnd, 2) = "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</pre>

sir same error 1004
CHill60 11-Dec-15 6:35am
   
I just responded to the solution you posted - the problem must be with your data. Share a (small!) sample of your data by using the Improve question link
CHill60 11-Dec-15 7:18am
   
I've added further information to my solution
Member 12200308 12-Dec-15 0:03am
   
sir can u give me code for this logic plz
CHill60 12-Dec-15 7:03am
   
I can't give you the code as I don't understand what you are actually trying to do ... are you just trying to insert a blank line before any row beginning with "br " ? Why do you have Resize(10-rwcnt) ?

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900