sumi - - Latest reply:
rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

Related:

- Vba insert rows based on count
- Insert based on count - Best answers
- Excel vba insert rows based on cell value - Forum - Excel
- Auto insert 'x' rows depending on value 'y' - Forum - Excel
- Excel vba insert picture based on cell value ✓ - Forum - Excel
- Excel vba select row based on cell value - Forum - Excel
- Excel - Vba inserting row and keep range membership - How-To - Excel

http://www.speedyshare.com/files/21270239/smpl.xls

take a look and see if u can relate to what I explained..!!

NOTE: th einitial value 6800 (in sample sheet) comes from another cell where user defines it .

-Sumi

take a look and see if u can relate to what I explained..!!

NOTE: th einitial value 6800 (in sample sheet) comes from another cell where user defines it .

-Sumi

http://www.speedyshare.com/files/21270372/smpl.xls

please refer this ..this has the subsheet along ..

please refer this ..this has the subsheet along ..

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

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

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

well A2ANEW_FIELDS is on sheet1 ..yes I suppose you are right I can use this piece of code by you ...

Another thing that I am really stuck at is .. rows counting is done in Sheet1 tats the number of rows I want to add in sheet2 and hence m calling it ( and the code is depending on my data here so its named sheet2 sorry abt the confusion ) ..now on sheet2 I dont want to add unecessary extra rows but only the required number of rows ...that will be cntofrowsonsheet1-countofrowsonsheet2.. I hope I am making sense.. !

Could you also help me with generating the seq on sheet1 for acc_num as u see the seq does not increment for everytime it encounters transfer_to in service_class

Another thing that I am really stuck at is .. rows counting is done in Sheet1 tats the number of rows I want to add in sheet2 and hence m calling it ( and the code is depending on my data here so its named sheet2 sorry abt the confusion ) ..now on sheet2 I dont want to add unecessary extra rows but only the required number of rows ...that will be cntofrowsonsheet1-countofrowsonsheet2.. I hope I am making sense.. !

Could you also help me with generating the seq on sheet1 for acc_num as u see the seq does not increment for everytime it encounters transfer_to in service_class

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

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

'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

Thanks for your help riz...

I am stuck at inserting numbers in sequence for col acc_num in sheet1...

If you see in my example there are 2 acc_nums repeated ... I want a code that looks for data in col. service_class and does not increment the seq of number in Acc_num when it encounters transfer_to string in col. 'service_class'....

Hope this makes sense...

would really appreciate your help ...

thanks a ton

-Sumi

I am stuck at inserting numbers in sequence for col acc_num in sheet1...

If you see in my example there are 2 acc_nums repeated ... I want a code that looks for data in col. service_class and does not increment the seq of number in Acc_num when it encounters transfer_to string in col. 'service_class'....

Hope this makes sense...

would really appreciate your help ...

thanks a ton

-Sumi

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

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

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

Heya...

thnx again but I neede VBA code for that and not a formula ... however I figured this one out ,.... its working good for me ..

Sub acc_insert()

Dim r As Range, cell As Range

Set r = Range("E16:E36")

Range("E15").Value = Range("J2").Value

For Each cell In r

If InStr(1, cell.Offset(0, 1), "transfer_to", vbTextCompare) = 0 Then

cell.Value = cell.Offset(-1, 0).Value + 1

Else

cell.Value = cell.Offset(-1, 0).Value

End If

Next

End Sub

---

If im not bothering u much can I ask another question plzz...

ACCIOU_FIELDS institution_id access_id acc_type acc_num name product_id

ACCIOU QATEAM ST1 DDA 6801

ACCIOU QATEAM ST1 DDA 6802 39

ACCIOU QATEAM ST1 DDA 6803

ACCIOU QATEAM ST1 DDA 6804

ACCIOU QATEAM ST1 SAV 6805

ACCIOU QATEAM ST1 SAV 6805 32

ACCIOU QATEAM ST1 SAV 6806

ACCIOU QATEAM ST1 SAV 6806

ACCIOU QATEAM ST1 LON 6807 3

ACCIOU QATEAM ST1 IRA 6808 9

ACCIOU QATEAM ST1 CD 6809 4

now I wish to insert value in sheet1.range("E9") for everytime I encounter institution_id as ST1 and for ST2 it will be sheet1.range("E10")....so name field will now have value in E9 11 times and similar case if its ST2... the value ST1 and ST2 come from sheet1.range("C9") and ("C10") respectively ....

thnx again but I neede VBA code for that and not a formula ... however I figured this one out ,.... its working good for me ..

Sub acc_insert()

Dim r As Range, cell As Range

Set r = Range("E16:E36")

Range("E15").Value = Range("J2").Value

For Each cell In r

If InStr(1, cell.Offset(0, 1), "transfer_to", vbTextCompare) = 0 Then

cell.Value = cell.Offset(-1, 0).Value + 1

Else

cell.Value = cell.Offset(-1, 0).Value

End If

Next

End Sub

---

If im not bothering u much can I ask another question plzz...

ACCIOU_FIELDS institution_id access_id acc_type acc_num name product_id

ACCIOU QATEAM ST1 DDA 6801

ACCIOU QATEAM ST1 DDA 6802 39

ACCIOU QATEAM ST1 DDA 6803

ACCIOU QATEAM ST1 DDA 6804

ACCIOU QATEAM ST1 SAV 6805

ACCIOU QATEAM ST1 SAV 6805 32

ACCIOU QATEAM ST1 SAV 6806

ACCIOU QATEAM ST1 SAV 6806

ACCIOU QATEAM ST1 LON 6807 3

ACCIOU QATEAM ST1 IRA 6808 9

ACCIOU QATEAM ST1 CD 6809 4

now I wish to insert value in sheet1.range("E9") for everytime I encounter institution_id as ST1 and for ST2 it will be sheet1.range("E10")....so name field will now have value in E9 11 times and similar case if its ST2... the value ST1 and ST2 come from sheet1.range("C9") and ("C10") respectively ....

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

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.

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")

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.

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