Microsoft Office Forums ashamed to having to ask

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-25-2019, 08:35 AM
johnsail johnsail is offline ashamed to having to ask Windows XP ashamed to having to ask Office 97-2003
Novice
ashamed to having to ask
 
Join Date: Jun 2019
Posts: 3
johnsail is on a distinguished road
Default ashamed to having to ask

Hi


Have attached single page sheet.
all I want to do is insert another row of cells AA to AL between current row and the red line and to copy the formulas.

Having gotten into all sorts of mess I have resorted to simple stage approach.

As you will see the first bit of code tries to insert AA to AL after entering a date in AA.

Cursor moves to AB alright but the the code continues to insert row after row of blank cells - moving current row down each time.

I cannot stop this happening???

help
Attached Files
File Type: xls engine test sheet.xls (152.5 KB, 2 views)
Reply With Quote
  #2  
Old 06-25-2019, 10:24 AM
NoSparks NoSparks is offline ashamed to having to ask Windows 7 64bit ashamed to having to ask Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 712
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Maybe something like this ?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    'limit target to a single cell at a time
    If Target.Count > 1 Then Exit Sub
    'limit to specific column
    If Target.Column = 27 Then
        'don't do anything unless target contains something
        If Target.Value <> "" Then
            'prevent changes made by this sub from triggering itself
            Application.EnableEvents = False
            'insert below row of target
            Target.Offset(1).Resize(1, 12).Insert (xlShiftDown)
            're-enable events
            Application.EnableEvents = True
        End If
    End If
End Sub
Reply With Quote
  #3  
Old 06-25-2019, 11:30 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline ashamed to having to ask Windows 7 64bit ashamed to having to ask Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Hi and welcome
in the future please use a title descriptive of your problem so that members can easily see what you are trying to do
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
Reply

Thread Tools
Display Modes



All times are GMT -7. The time now is 02:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft