?
Solved

how to insert a new row in the excel sheet based on a condtion

Posted on 2012-03-26
6
Medium Priority
?
225 Views
Last Modified: 2012-04-22
how to insert a new row in the excel sheet based on a condtion
For example I need to insert the following line every time there is m in column 1:

*/ ANNONAME:"YT_West_F683.flt"

A copy of my excel sheet is attached

                             
m      748791.3125      2742759.5      1      0      YT_West_F683.flt
d      748675.625      2742808      1      0      YT_West_F683.flt
d      748552.375      2742842      1      0      YT_West_F683.flt
m      741813.3125      2755022      1      0      YT_West_F680.flt
d      741707.25      2755088.75      1      0      YT_West_F680.flt
d      741618.3125      2755188      1      0      YT_West_F680.flt
d      741500.75      2755233.5      1      0      YT_West_F680.flt
d      741397.125      2755305      1      0      YT_West_F680.flt
d      741303      2755394.5      1      0      YT_West_F680.flt
d      741194.125      2755456      1      0      YT_West_F680.flt
d      741087.125      2755521      1      0      YT_West_F680.flt
d      740980.25      2755586.5      1      0      YT_West_F680.flt
m      754808      2751540.5      1      0      YT_West_F548.flt
d      754703      2751609.5      1      0      YT_West_F548.flt
d      754599.25      2751680.75      1      0      YT_West_F548.flt
d      754504.25      2751768.75      1      0      YT_West_F548.flt
d      754397.875      2751835      1      0      YT_West_F548.flt
d      754289.1875      2751897      1      0      YT_West_F548.flt
d      754189.0625      2751975      1      0      YT_West_F548.flt
d      754087.625      2752051      1      0      YT_West_F548.flt
d      753983.25      2752121      1      0      YT_West_F548.flt
d      753882.625      2752198.25      1      0      YT_West_F548.flt
d      753780.125      2752272      1      0      YT_West_F548.flt
d      753677.125      2752345      1      0      YT_West_F548.flt
d      753573.625      2752416.5      1      0      YT_West_F548.flt
d      753472.5      2752493      1      0      YT_West_F548.flt
d      753375.875      2752577.5      1      0      YT_West_F548.flt
d      753268.875      2752642.75      1      0      YT_West_F548.flt
d      753139.6875      2752665.75      1      0      YT_West_F548.flt
m      756466.875      2741516      1      0      YT_West_F547.flt
d      756335.375      2741534.5      1      0      YT_West_F547.flt
d      756246.75      2741634.5      1      0      YT_West_F547.flt
d      756132.1875      2741685.25      1      0      YT_West_F547.flt
d      756035.6875      2741770.25      1      0      YT_West_F547.flt
d      755924.375      2741827.25      1      0      YT_West_F547.flt
d      755846.375      2741947.5      1      0      YT_West_F547.flt
m      748991.375      2753368      1      0      YT_West_F543.flt
d      748913.3125      2753488.5      1      0      YT_West_F543.flt
d      748810      2753560.5      1      0      YT_West_F543.flt
d      748721      2753660      1      0      YT_West_F543.flt
d      748598      2753694.5      1      0      YT_West_F543.flt
d      748494.8125      2753767      1      0      YT_West_F543.flt
d      748394.75      2753845      1      0      YT_West_F543.flt
test.xlsx
0
Comment
Question by:Mohammed Dallag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37764644
you want to loop through the records set?

for each style?

then if col1 = m, insert a new row?  below the row with m in?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37764661
Sub InsertAnnote()
Dim strInsert As String
strInsert = Range("A1").Value
Range("A2").Activate
    While ActiveCell.Value <> ""
        If ActiveCell.Value = "m" Then
            ActiveCell.Offset(1, 0).Activate
            If ActiveCell.Value <> strInsert Then
                Selection.EntireRow.Insert
                ActiveCell.Value = strInsert
            End If
        End If
        ActiveCell.Offset(1, 0).Activate
    Wend
End Sub

Open in new window

edit: added a check to see if m line already annotated..
test.xls
0
 

Author Comment

by:Mohammed Dallag
ID: 37764838
Thank you but as you notice in the file that the annotation is diferent for each m.
I need to read the annotation from the 6th column corsponding to the right m and have it in the following format  
*/ ANNONAME:"column 6"

Regards,

Dallag
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 37768796
so the annotation needs to be a concatenation of */ ANNONAME:"<insert content of previous records column6 value>"?

Sub InsertAnnote()
Dim strDefault As String
strDefault = "*/ ANNONAME:" & Left("""", 1) & "<insert>" & Left("""", 1)
Dim strInsert As String
strInsert = ""
Range("A2").Activate
    While ActiveCell.Value <> ""
        If ActiveCell.Value = "m" Then
            ActiveCell.Offset(1, 0).Activate
            strInsert = Replace(strDefault, "<insert>", ActiveCell.Offset(-1, 5).Value, 1, 1)
            If ActiveCell.Value <> strInsert Then
                Selection.EntireRow.Insert
                ActiveCell.Value = strInsert
            End If
        End If
        ActiveCell.Offset(1, 0).Activate
    Wend
End Sub

Open in new window

test.xls
0
 

Author Closing Comment

by:Mohammed Dallag
ID: 37858083
Excellent
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37878431
my pleasure :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question