[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2012-03-26
6
Medium Priority
?
229 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

650 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