Insert rows based on count of row in another [Solved/Closed]

- - Latest reply: rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Mar 5, 2010 at 05:26 PM
Hello,
I have a excel where I create few dummy accounts and the next sheet (sheet2) has details of these accounts.
I need the sheet 2 to have certains data copied from sheet1 and have the same number of rows created as on sheet1 (defined in range)...

could you please help me to write this on VBA macro ...


-Sumi
See more 

6 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
0
Thank you
More information is needed.
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 ..
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
> sumi_10 -
Could you post a sample workbook at some shared location. Though you did explain, I am still confused. May be looking at sheet would help. You can put on any share site like http://www.speedyshare.com/
Posts
5
Registration date
Thursday March 4, 2010
Last seen
March 5, 2010
0
Thank you
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
Posts
5
Registration date
Thursday March 4, 2010
Last seen
March 5, 2010
0
Thank you
http://www.speedyshare.com/files/21270372/smpl.xls

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
Posts
5
Registration date
Thursday March 4, 2010
Last seen
March 5, 2010
0
Thank you
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
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
Posts
5
Registration date
Thursday March 4, 2010
Last seen
March 5, 2010
0
Thank you
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
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
Posts
5
Registration date
Thursday March 4, 2010
Last seen
March 5, 2010
0
Thank you
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 ....
rizvisa1
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.