Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
68 Posts
Discussion Starter · #1 ·
WIndow 2007, XP

Hello,

I need some help with an Excell formula.

I need to add a new line everytime a particular cell in a line has a certain value.

Example:

Prod Teapot ID400 8

If colum D=8, add a new line and populate cell 2B=SP and cell 2C - XXXX. The end result would look like this.

Prod Teapot ID400 8
.........SP.......XXX


Thanks in advance.

PS: I don't know how to past my spreedsheed into a post so the colums line up so having to use "." to seperate them. If someone knows a better way to post this, suggestions are welcome.
 

·
Registered
Joined
·
68 Posts
Discussion Starter · #3 ·
Hello,

I'm afraid I have no idea what a changesheet event is. When I googled it, it indicated that it is used to trigger an event when the contents of a cell changes.

Let me ask this a differant way.

What I'm looking for is either code or a formula that will add a new line whenenver the contents of a partuculr cell = a particular value and once the new line is added, populate some fo the cells.

For example cell D1 and cell D2 contain a value of 8.

Before:
ORD Teapot ID400 8
ORD Cream ID401 8

Insert a new line after the 1st occurance of an 8 in colum D and populate cell A with SPP and cell B with ZZZ on the new line that was inserted.

After:
ORD Teapot ID400 8
SPP ZZZ
ORD Cream ID401 8
SPP ZZZ
 

·
Registered
Joined
·
68 Posts
Discussion Starter · #5 ·
Some of the lines do not have an 8 in colume D. IF D1=8, add new line and populate the fields. If D1 does not equl 8 than do nothing. Again I have no idea how to write this.

Thanks for responding. I've always had good luck here which is why I posted my quesiton here 1st.. I'll see if I can find another forum or do a search for a difernat type of instruction.
 

·
Registered
Joined
·
9 Posts
It's easier to understand if you submit an attachment with dummy data and
more detailed instructions.
Are there many rules (conditions) ? , where do you plan to keep them (say a VLookUp table,
a hidden or not column, within a macro, etc.
How familiar are you with Excel macros ?
 

·
Registered
Joined
·
5,458 Posts
To continue to do this (not including what is already entered), use this as a change event...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("D:D")) Is Nothing Then Exit Sub
    If Target.Value = 8 Then
        If Me.Cells(Target.Row + 1, 1).Value <> "" And _
           Me.Cells(Target.Row + 1, 2).Value <> "SP" And _
           Me.Cells(Target.Row + 1, 3).Value <> "XXXX" And _
           Me.Cells(Target.Row + 1, 4).Value <> "" Then
            Me.Rows(Target.Row + 1).Insert
            Me.Cells(Target.Row + 1, 2).Value = "SP"
            Me.Cells(Target.Row + 1, 3).Value = "XXXX"
        End If
    End If
End Sub
To use, right click your sheet tab and select 'View Code', paste in the right pane (should be big and white, we call this the Code Pane).

If you want to perform this action to an existing list, as stated earlier, you need a sub routine for this.

This code goes into a standard module...

Code:
Option Explicit

Sub InsertLinesAfterValue()

    Dim WS As Worksheet
    Dim iStep As Long
    Dim iLastRow As Long

    Call TOGGLEEVENTS(False)
    
    Set WS = ActiveSheet
    iLastRow = WS.Cells(WS.Rows.Count, "D").End(xlUp).Row

    'change "To 2" to whatever the first row of data is
    For iStep = iLastRow To 2 Step -1
        If WS.Cells(iStep, 4).Value = 8 Then
            If WS.Cells(iStep + 1, 1).Value <> "" And _
               WS.Cells(iStep + 1, 2).Value <> "SP" And _
               WS.Cells(iStep + 1, 3).Value <> "XXXX" And _
               WS.Cells(iStep + 1, 4).Value <> "" Then
                WS.Rows(iStep + 1).Insert
                WS.Cells(iStep + 1, 2).Value = "SP"
                WS.Cells(iStep + 1, 3).Value = "XXXX"
            End If
        End If
    Next iStep

    Call TOGGLEEVENTS(True)
    
End Sub

Sub TOGGLEEVENTS(blnState As Boolean)
'Originally written by Zack Barresse
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub
As you can see, it basically does the same thing. I wouldn't use the change event to call the other (second) routine, because that means you'd be looping through every row in the sheet on every change to column D. Instead, you could use the second code to run once now, then with the change event in place, it would take care of all other future entries.

There is a second routine I posted in the second set of code, called TOGGLEEVENTS. I would recommend keeping this in a standard module. Do you see how it's called in the routine right above it? There is a line above the bulk of the code calling it and passing a False variable, and there is a line prior to the 'End Sub' line which passes a True variable. This will turn some application settings off and back on to optimize code.

NOTE: I did not add this to the change event, but it is very important to add. Why did I leave that out? Well, it will help you learn a bit of VBA coding! If you still can't get it, we can post the full code no problem. What you'll need to do is:

1) Ensure you have the TOGGLEEVENTS routine in a Standard Module in that workbook (Insert menu -> Module)
2) Add a line after the opening line of the change event, as well as after the first two checks (hint: after this line - If Target.Value = 8 Then) - "Call TOGGLEEVENTS(True)"
3) Before the 'End Sub' line of code, add the same ending line - "Call TOGGLEEVENTS(False)"

If you'd like to step through your code you can do so with the F8 key. On a change event, if you'd like to step through your code, it's easiest to put a breakpoint at the opening line (put your cursor on that line and hit F9). Remember, the keyboard shortcut for opening the Visual Basic Editor is Alt + F11.

Post back if you need more help.
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top