1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

how to increment within a macro

Discussion in 'Microsoft Office Specialist (MOS)' started by sirdragon, Jan 16, 2005.

  1. sirdragon

    sirdragon Nibble Poster

    70
    0
    11
    hi guys maybe you can help with this one.

    I have a macro that inserts a row in a workbook when a cell is changed.

    the problem is i want it to increment with the change, so that when range E19 is changed a new row is entered, so the formula is incremented to next row. when that is changed the formula increases by one etc.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Intersect(Range("E26"), Target) Is Nothing Then Exit Sub'this needs to increment by one each time it is fired

    If IsEmpty(Range("E26")) Then Exit Sub 'also increment by one

    Application.EnableEvents = False

    Range("E21").Select
    Selection.EntireRow.insert
    Range("E18").Select

    Application.EnableEvents = True
    End Sub


    hope you can point me in right direction

    cheers
     
  2. sirdragon

    sirdragon Nibble Poster

    70
    0
    11
    Not to worry guys

    have it sorted now

    Code:
    Sub newrow()
    '
    ' newrow Macro
    ' Macro recorded 18/01/2005 by Perera-Sweetman
    '
    Application.EnableEvents = False
     
    Worksheets(1).ScrollArea = ""
     
    ActiveSheet.unprotect
    Range("Insert").Offset(RowOffset:=-1, columnOffSet:=0).Select 
    Selection.EntireRow.Insert
    Range("Insert").Offset(RowOffset:=-2, columnOffSet:=-2).Select 'places cursor for entrys
     
     
    ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Worksheets(1).ScrollArea = "range1:range2"
    Application.EnableEvents = True
    End Sub
    
    [​IMG]
     

Share This Page

Loading...