+ Reply to Thread
Results 1 to 12 of 12

Need help in Macro build

  1. #1
    Registered User
    Join Date
    12-11-2006
    Posts
    12

    Need help in Macro build

    I need a macro to insert a new row with incremented serial number in column A, when done the first time - to insert the row at a specified cell starting with serieal no: 1 and later to insert the new row after the last row. Also I need the macro to insert certain pre-defined text values in specified cells in the new row..

    Can anyone help me with this macro please

    Venkat

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    A macro can be built to do what you require but you will need to post a lot more details of exactly what is required

  3. #3
    Registered User
    Join Date
    12-11-2006
    Posts
    12

    Smile Can I attach the XL file??

    Hi Mudraker,

    Thank you very much for the immediate response.. I am currently using a spreadsheet with this facility contained in a macro that is password protected.. I don't intend to unlock the macro (I presume the macro has several other functions too built into it).. What I need is the insert new row the way this macro does when the "New Action" button is clicked..

    I am attaching the compressed file of the XL file I have.. so you could open it and have a handson feel of the facility I am talking about..

    However I will detail the facility I need step by step if you feel it is needed even after taking a look at what is contained in the XL file attached.

    I will be thankful to you for your help.. as I need the code so that I wil be able to modify it further to define the specific text input that needs to go into different cells as per the changing needs..

    Thanks and cheers

    Venkat
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Venkat

    As the macros are locked I can not see how they are constructed and I will not run a unkown macro on my pc.

    You will need to unlock the macro or provide full details.

  5. #5
    Registered User
    Join Date
    12-11-2006
    Posts
    12
    Hi Mudraker,

    My very problem is that I am unable to open the macro and hence I am seeking help from this expert forum.. What I am doing is sending you the gif file giving the image of the XL screen..

    Let me explain what happens when I click on the "New Action" button in the sheet..

    The macro checks the last row with an empty cell in Column A and inserts a new row there with the serial number incremented compared to the value in the cell above.. it also inserts texts "Open" and "Current Date" in the columns cells of 'Status' and 'Date Assigned' which are essentially C and H columns..

    Now my problem is that I am only able to use the macro as is and hence am having to format the work sheets around these fixed values.. where as the nature of different projects and the needs keep varying and it will be a great help if I can change the 'text' and their corresponding position in the new row inserted as per my needs.

    Please take a look at the image I am attaching herewith and let me know if you can help me with the code..

    Thanks and regards

    Venkat
    Attached Images Attached Images

  6. #6
    Registered User
    Join Date
    12-11-2006
    Posts
    12
    Hi Bryan,

    I can not code in VBA.. however I am reasonably familiar with the way macros work etc., I am unable to open the macro as it is 'passwrod protected'.. I did try to open it with couple of tools (trial versions) available for free download..

    And this macro also has many other functions which are not very useful for me.. All I need is this single facility of using the 'New Action' button so that when I allow the Project team members to access the sheet directly the input is standardized and we have a common sheet to track the items..

    Hope you can help me with this..

    Thanks

    Venkat

  7. #7
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Venkat

    With out the password to open the macros you are not going to be able to add, modify or delete macros from this workbook.

    You have 2 options

    Option 1 is to somehow find the password for the workbook.

    Option 2 is to build a new workbook from scratch.

    In either case this is the code you will need


    Sub AddNew()
    Dim lNextRow As Long

    lNextRow = Cells(Rows.Count, "a").End(xlUp).Row + 1
    Cells(lNextRow, "a").Value = Cells(lNextRow - 1, "a").Value + 1
    Cells(lNextRow, "c").Value = "Open"
    Cells(lNextRow, "h").Value = Date

    End Sub

  8. #8
    Registered User
    Join Date
    12-11-2006
    Posts
    12
    Hi Mudraker,

    Thank you very much for the help.. Yes I am able to build a new workbook from the scratch with the code you gave me.. Now I will be able to modify it suitably as and when needed..

    It is indeed a great help..

    Cheers
    Venkat

  9. #9
    Registered User
    Join Date
    12-11-2006
    Posts
    12

    Unhappy

    Hi Mudraker,

    I have this problem with the macro when I am using it with a new spreadsheet.. (earlier I tried replacing the old macro with the one you gave me and it worked fine as the table already has a last row with a valid number).. where the current last row doesn't have a numerical value.. As I understand you assigned the value to increment from that of the previous row..

    Could you please help me with this condition where it doesn't find a numberical value.. I have text in the column head.. it starts the first row with the number "1" and there on it increments to the next value..

    Sorry for the trouble.. As I mentioned earlier my VBA coding capabilities are minimal..

    Thanks and cheers

    Venkat

  10. #10
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Venkat


    Try this version or simply add your 1st row data manually then run the previous version which will then work ok.

    Sub AddNew()
    Dim lNextRow As Long

    lNextRow = Cells(Rows.Count, "a").End(xlUp).Row + 1
    If IsNumeric(Cells(lNextRow - 1, "a").Value) Then
    Cells(lNextRow, "a").Value = Cells(lNextRow - 1, "a").Value + 1
    Else
    Cells(lNextRow, "a").Value = 1
    End If
    Cells(lNextRow, "c").Value = "Open"
    Cells(lNextRow, "h").Value = Date

    End Sub

  11. #11
    Forum Contributor
    Join Date
    12-04-2003
    Posts
    360
    you ok mudraker?

  12. #12
    Registered User
    Join Date
    12-11-2006
    Posts
    12

    Smile

    Thanks Mudraker.. That code works..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1