Microsoft Office Forums

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, 6 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: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Other Forums: Access Forums

All times are GMT -7. The time now is 04:52 PM.


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