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