Insert rows based on count of row in another

Hi ...

I have sheet 1 that has 22 rows generated I am able to get the count with this function I wrote

i have to count rows only between two rows that I m searching with below strings.


Public Function cnt()
Dim c As Integer
i = 5
j = 1
count = 0
Do While Cells(i, j) <> "A2ANEW_FIELDS"
i = I + 1
Loop

Do While Cells(i, j) <> "FILEND_FIELDS"
i = I + 1
count = count + 1
Loop
count = count - 1

cnt = count
----------------------

now I am able to insert rows in another sheet based on output of this function but ...

Sub insertrow()

' Set numrows = number of rows of data.

numrows = Sheet2.cnt




Sheet1.Activate
Sheet1.Range("A10").Select
ActiveCell.Resize(numrows, 1).EntireRow.Insert

end sub
---------------------

But now everytime I run this macro I have 22 rows inserted in my sheet2... I want to avoid this happening ..
Will it be possible to count the number of rows present in sheet2 between 2 strings in a column 'ACCIOU_FIELDS' and ending with 'BALNEW_FIELDS' and then insert the required number of rows instead of 22 (or wtvr the cnt function returns) everytime the macro is run ??


appreciate ur help ..

Ok one thing that I am not clear on

Why do you have function cnt defined in sheet2 ? When it gets executed, it is looking at sheet 2 data. I must be missing some thing because I dont see any "A2ANEW_FIELDS". Its on sheet one. Is my version of excel playing tricks here

Also you really dont need a function to calc the rows in between

In your sub routine you can add these three lines to find out number of rows between the two words. Only thing is that both words must be there and that ACC must come before BAL

StartAt =WorksheetFunction.MATCH("ACCIOU_FIELDS",Range("Sheet2!A:A"),0)
EndAt = WorksheetFunction.MATCH("BALNEW_FIELDS",Range("Sheet2!A:A"),0)

numrows= EndAt - StartAt -1

You can do the same thing for row counts

'ROWS ON SHEET 1
Sheet1StartAt =WorksheetFunction.MATCH("A2ANEW_FIELDS",Range("Sheet1!A:A"),0)
Sheet1EndAt = WorksheetFunction.MATCH("FILEND_FIELDS",Range("Sheet1!A:A"),0)
Sheet1Row = Sheet1EndAt - Sheet1StartAt -1

'ROWS ON SHEET 2
Sheet2StartAt =WorksheetFunction.MATCH("ACCIOU_FIELDS",Range("Sheet2!A:A"),0)
Sheet2EndAt = WorksheetFunction.MATCH("BALNEW_FIELDS",Range("Sheet2!A:A"),0)
Sheet2Row = Sheet2EndAt - Sheet2tartAt -1

'difference in row counts (assumption is sheet1 will always have more or same number of rows as sheet 2
Sheet2numrows= Sheet1Row - Sheet2Row

As for seq number, sorry I have no idea what you were asking

Ok I dont know how you are getting 6800, so I am hard coding it. You can point it to the right location

but you can put this formula in E15

=6800 + COUNTIF($F$15:$F15,"<>transfer_to") -1

Drag this formula down to last data line

This formula is assuming that your first row is 15

From what I understood from your question

Sheet2StartAt = WorksheetFunction.Match("ACCIOU_FIELDS", Range("Sheet2!A:A"), 0)

Sheet2EndAt = WorksheetFunction.Match("BALNEW_FIELDS", Range("Sheet2!A:A"), 0)


st1count = WorksheetFunction.CountIf(Range(Sheets("Sheet2").Cells(Sheet2StartAt + 1, "C"), Sheets("Sheet2").Cells(Sheet2EndAt - 1, "C")), "=ST1")

st2count = Application.WorksheetFunction.CountIf(Range(Sheets("Sheet2").Cells(Sheet2StartAt + 1, "C"), Sheets("Sheet2").Cells(Sheet2EndAt - 1, "C")), "=ST2")


'check if you do want to add to existing value in e9/e10 this new value or not. I am presuming you do
Sheets("Sheet1").Range("E9") = Sheets("Sheet1").Range("E9") + st1count
Sheets("Sheet1").Range("E10") = Sheets("Sheet1").Range("E10") + st2count


On the side note, your acc_insert is hard coded to rows. If rows changes, your macro would fail. Like in above routine, I am using same old values, to find out number of rows b/w the two words (as you did earlier) and that defines my range of rows.