Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 63

Thread: Solved: Insert Row Macro/VBA

  1. #21
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi John,
    Seems this thread is getting off track. Larry needs to understand that the help offered here is free and that rudeness is not conducive to getting answers to questions which is what we are supposed to be doing, not providing working applications.

    Larry, there is lots of help available here but you are expected to do some of the work yourself and maintain a civil decorum. I have been following this thread and you have basically asked for someone to solve your problem for you and John has gone above and beyond, submitting efforts repeatedly to try to help you. You have contributed only that what John has offered has not worked for you. It takes time and patience to work out problems like this through postings so please be patient and civil and I'm sure this can be sorted out.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  2. #22
    Steve
    Just exactly where have I been uncivilized?
    I have been working on this problem for a couple of weeks to no avail.
    I am not an expert in Excel, I am a Basic user trying to learn and gain a better understanding of VBA. I have courteously asked a very basic Question.
    Quote Originally Posted by LarryLaser
    can you go into depth a little about the arguments?
    for a very good reason, trying to understand what each argument is suppose to do.
    If you consider this to be rude then please excuse me.
    Last night I worked on the code in the workbook till 4 in the morning trying to make the mod's Johnske commented about and have gotten nowhere.
    If you have a better suggestion, Please, be my guest.
    Larry
    LarryLaser

    neogeek in training

  3. #23
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi Larry,
    your response in post 20 is what triggered my response, especially after I read John's post 19 and noticed you hadn't even downloaded his attachment (0 downloads) and it seems to do exactly what you asked for it to do.

    I may be missing something but you will have to look at it and let us know if we're still missing something.....
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #24
    Hey Steve
    I download and tried it. The cell protection function is staying the same, that much is true. But nothing is happening when the last cell changes in the target column. That is why I keep asking the same Question
    can you go into depth a little about the arguments?
    If I could just get a better understanding of what is being attempted I could figure out what I need to mod to make it work the way I need.
    When I enter data nothing happens, the active cell jumps to home (1st cell in the data entry range) no insert, no copy, nothing.

    Let me try to explain differently; Six of the sheets will change by the users data entry, here is just one of them.







    Sheet7; B2:K~(~ = However many rows needed)
    B2:K8 locked =header info
    B9:H~ unlocked =data entry for user
    I9:K~ Locked & Hidden =auto functions & formulas

    Last 3 rows in the sheet are the totals
    G~:K~ locked =totals data is transferred to Statement (sheet1)






    Columns "B-D" always have data, "E:H" can have data (one must), "I" and "J" are conditional (depending on the entry in column "B", "K" is the total of the row from "E:J" (it also totals on condition of the status of "B")
    Column formats:
    "B" = Number, Validation (Tax code setting based on sheet2)
    "C" = Date
    "D" = Text (receipt details)
    "E:H" = data entry, Currency, (always accessible to the user, Once completed the whole workbook will be locked and password protected, havent got that far yet)
    "I:K" = Row SubTotals & Totals, Locked, Hidden.






    The type of career this Expense workbook is for is not the usual you may have seen. Myself and a number of my friends have worked in Heavy Construction, We work in different parts of the country during the same year. Tax codes and rates change for the area we are in, (I have worked in as many a 9 states in one year) I have never been able to find any Program (or for that matter) a CPA that fully understands the Deductability of any thing you spend when you are out in the field. And none of my friends have either. The State and Federal Tax codes change all the time and It really affect those of us who travel from state to state (even county to county).

    So we need to be able to Itemize every penny and place it in the right category for our tax's to work out right.

    edit, Also I am the only one, of all of my friends that has experiance with coding and App development, (and I am pretty Green when it comes to VBA and the .NET platform)
    LarryLaser

    neogeek in training

  5. #25
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Ok Larry,
    Looks and sounds like your sheet protection is correct and now your trying to get the add row to trigger on the last cell of a row....right?

    Looking only at the vehicle data sheet, if you look at the code for the sheet which is supposed to add a row you will find it is set to trigger in column K....which is a protected cell with a formula...

    It works for me if I change the K to a J in Johns code for the sheet.....in the vehicle data sheet. Thats the only sheet I looked at.

    [vba]
    Const LastCol As String = "$J"
    [/vba]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #26
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Further testing shows some work needed on getting the totals and subtotal forumlas to update with the added rows...the rows however are getting their new formula's correct.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  7. #27
    Hey Steve
    I have done this on other sheets and had the same success this way, But;
    My question is this, Can I not get this function to kick when the totals cell "M" for the last row changes (cell being protected) form 0.00 to any #>?
    The problem is that, If you have to correct any data in the range of DataEntry, and you happen to tab across the target column or select ("J" in this case) the VBA code fires and you get a new blank row in the middle of the data range.
    LarryLaser

    neogeek in training

  8. #28
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Yes I see what your getting at but I don't see an easy solution yet....its not that hard to get rid of the extra rows if you comment out the sheet code and unprotect the sheet.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #29
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I tried changing it to m but the formula change doesn't seem to trigger it.......
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #30
    True enough, that is an issue that I don't have a problem with, but my friends who I am creating this with are not even beginners, Most of them have never used Excel before and I would like their experiance to not be confusing and frustrating.

    can something like this be done??
    [vba]
    If Target.Cells.Count > 0.00 Then Exit Sub
    [/vba]
    and if it can, what code changes would I need to make in the target range??
    LarryLaser

    neogeek in training

  11. #31
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by lucas
    Yes I see what your getting at but I don't see an easy solution yet.....
    Steve, another worksheet event can take care of that

    [VBA]
    '<< EG for sheet "vehicle data"
    Option Explicit
    Private Changed As Boolean
    '
    Private Sub Worksheet_Activate()
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
    End Sub
    '
    Private Sub Worksheet_Change(ByVal Target As Range)
    Changed = True
    End Sub
    '
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '
    Dim Cell As Range
    '**************************
    'put your own last column below
    Const LastCol As String = "$K"
    '**************************
    '
    If Target.Row = 1 Then Exit Sub
    '
    If Changed = True And Target.Row = Range("B" & Rows.Count).End(xlUp).Row Then
    '
    On Error GoTo Finish
    ActiveSheet.Unprotect password:=""
    '
    '//if reached the last entry in this row
    If Left(Target.Address, 2) = LastCol Then
    Application.EnableEvents = False
    '
    '//insert a new row
    Rows(Target.Row + 1).Insert Shift:=xlDown
    '
    '//copy the row
    Rows(Target.Row).Copy
    '
    '//paste the formats in the new row
    Rows(Target.Row + 1).PasteSpecial xlPasteFormats
    Rows(Target.Row + 1).Borders(xlEdgeTop).LineStyle = xlNone
    '
    With Range("M" & Target.Row + 1).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    End With
    '
    '//copy the formulas to the new row
    For Each Cell In Range("B" & Target.Row, "M" & Target.Row)
    If Cell.HasFormula Then Cell.Offset(1, 0) = Cell.FormulaR1C1
    Next
    '
    '//select column A in the new row for next entry
    Range("B" & Target.Row + 1).Select
    Changed = False
    End If
    '
    End If
    Finish:
    Application.EnableEvents = True
    ActiveSheet.Protect password:=""

    End Sub
    [/VBA]
    John
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  12. #32
    John
    this works to protect the cells that need protection and inserts row only if last row is blank when data entry commences, and the user tabs accross the last column cell is selected in the code "that is not protected".

    good enough for now.
    Thanks for your help
    Larry
    LarryLaser

    neogeek in training

  13. #33
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Thanks for catching up on the thread and picking up my slack John.
    I haven't checked it out the latest yet, short on time.

    Larry, glad to hear that you got it sorted for now.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  14. #34
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    BTW Larry, a related but completely separate issue here... I notice your worksheet code for your Sub Totals (and Totals) is of the form K21=SUM(E9:H20,J9:J20).

    I would assume that you want any data on new rows to also be included in the sum after a new row is inserted. In that case you'll need to change the worksheet formulas so they're of the form K21=SUM(E9:OFFSET(H21,-1,0),J9:OFFSET(J21,-1,0))
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  15. #35
    Hey John
    Not sure I follow what you mean?? Which Sheet are you talking about, and can you explain the advantage of using the =offset v basic =sum function??
    1 *note* if you are talking about the Vehicle data (!Sheet 3), one column is not currency value (I), and does not add to the totals or subtotals. (a part that I did not complete on the "wkbk" I posted.

    Thanks John
    Larry
    LarryLaser

    neogeek in training

  16. #36
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Yes, it was the vehicle data sheet I looked at.

    Basically, the formula for the sub total in K21=SUM(E9:H20,J9:J20) gives the sum of all the amounts in columns E, F, G, H, and J from the 9th to the 20th row (inclusive) and this formula is fixed.

    So, even though you may have added rows so that the sub total is now in row 201, that sub total will not change - it won't add any data from those columns that may be in rows 21 to 200 (the new rows).

    What the offset does is to add the new data from the new rows to the sub total. The offset simply refers to the range that is to be summed. As a simpler example, let us say you have data in rows 9 to 15 and a sub total in column K, row 16 that is the sum of the data K9 to K15... the formula K16=SUM(K9:K15) will give the sum but when you add a new row, the sub total formula that's now in K17 will remain K17=SUM(K9:K15).

    However K16=SUM(K9:OFFSET(K16,-1,0)) gives the same sum, as OFFSET(K16,-1,0) is simply K15 and when you add a new row with this formula, the sub total formula in K17 will become K17=SUM(K9:OFFSET(K17,-1,0)), and OFFSET(K17,-1,0) is simply K16, so this is: K17=SUM(K9:K16) and any data from the new row has been included in the sum.

    The minus one in the offset refers to 'the row above', and the zero refers to 'the same column' (K)

    HTH
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  17. #37
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi Larry,
    Hope you follow what John is saying....the subtotal cells are not adding the new rows basically.

    I hope you post the final solution for this when you get it done....some might find it useful.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  18. #38
    Thanks John
    The more "in depth" explanation of the function and arguments Really helps. I really need to gain a better understanding of VBA language.

    Can I ask you another question??

    Can you define this argument for me, especially the "2"..?
    [VBA] If Left(Target.Address, 2) = LastCol Then [/VBA]
    LarryLaser

    neogeek in training

  19. #39
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Left(Target.Address, 2)... the 2 refers to the first two letters or numbers in the targets address, i.e. the column letter (c.f. Right(Target.Address, 2) would refer to the last two letters or numbers).

    If you had a large number of cells in a row so that you got into the AA, AB, AC, ..., etc columns you would need to use Left(Target.Address, 3) to get the correct column letter.
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  20. #40
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Actually, the "Left" above was left-over from code that was modified to suit this. Now that it appears to be working correctly we can do a little to optimize this so that it's more efficient (this automatically inserts a new row as soon as a formula is selected and then only copies formulas from the cells that have formulas)...
    [vba]
    '<< EG for sheet "vehicle data"
    Option Explicit
    Private Changed As Boolean
    '
    Private Sub Worksheet_Activate()
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
    End Sub
    '
    Private Sub Worksheet_Change(ByVal Target As Range)
    Changed = True
    End Sub
    '
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '
    Dim Cell As Range
    '
    '**************************
    'enter the last FORMULA column below
    Const LastFormulaCol As String = "M"
    '**************************
    '
    If Target.Row = 1 Then Exit Sub
    '
    '//if past the last manual entry in this row
    If Changed = True And Target.Row = Range("B" & Rows.Count).End(xlUp).Row And Target.HasFormula Then
    '
    On Error GoTo Finish
    ActiveSheet.Unprotect password:=""
    '
    Application.EnableEvents = False
    '
    '//insert a new row below
    Rows(Target.Row + 1).Insert shift:=xlDown
    '
    '//copy the row
    Rows(Target.Row).Copy
    '
    '//paste the formats in the new row
    With Rows(Target.Row + 1)
    .PasteSpecial xlPasteFormats
    '//get rid of the copied unwanted heavy line
    .Borders(xlEdgeTop).LineStyle = xlNone
    End With
    '
    '//restore the thin border in the "M" column
    With Range("M" & Target.Row + 1).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    End With
    '
    '//copy the formulas to the new row
    For Each Cell In Range(Target.Address, LastFormulaCol & Target.Row)
    Cell.Offset(1, 0) = Cell.FormulaR1C1
    Next
    '
    '//select column B in the new row for next entry
    Range("B" & Target.Row + 1).Select
    Changed = False
    End If
    '
    Finish:
    Application.EnableEvents = True
    ActiveSheet.Protect password:=""
    End Sub

    [/vba]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •