Solved

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

Posted on 2012-03-26
6
221 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:dallagmm
  • 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:dallagmm
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 15

Accepted Solution

by:
Simon Ball earned 500 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:dallagmm
ID: 37858083
Excellent
0
 
LVL 15

Expert Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now