+ Reply to Thread
Results 1 to 29 of 29

Code to make changes to a sheet

  1. #1
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Code to make changes to a sheet

    I have a worksheet with a column that has certain job descriptions. DL, IDL, MOH, G&A, and Other MOH. Basically I don't need all of these and would like a macro to:

    1 Delete All Rows With status INATIVE from column I
    2 Add a column between Columns I and J
    3 Move columns K and Q to the right of Column I
    4 Do a vlookup in column L (=VLOOKUP(I2,'DL List'!A2:B34,2,0)) and convert the formula to values in column L
    5 If the description is DL in column K then it should be DL in column L all other descriptions in column K (IDL, MOH, G&A, and Other MOH) should be converted to IDL in column L

    Can someone please give me a way to start this code?

  2. #2
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    If you're interested in trying to do it yourself then:
    For 1 & 5, use something like
    Please Login or Register  to view this content.
    Then for the rest, use the macro recorder to find out what the commands are and modify them as you like.

    If you'd prefer to learn by seeing how someone else would write the code on a sample, then provide a sample.
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  3. #3
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Hello thanks for the reply. I have attached a sample file of what I need... The sheet Raw is how the data comes, the sheet final is what I need to do and DL List has a table to do a vlookup there I have found a macro which will help me with part of the code but it moves the columns all the way to columns A and B and cant seem to adapt it to move them where they are in the Final sheet. As for your code it would work but as long as the vlookup is done first as the table will eliminate doubt on the DL employees... Please see sample for reference Thanks
    Last edited by Leith Ross; 10-23-2019 at 08:47 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    This will do what you said. Note that step 4 is unnecessary, since you overwrite all of column L afterwards.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Thanks K64 for the response but there is only one problem I detected ... when I did the count of DL vs IDL in the "L" Column, the results are very different... True number manually DL = 4737 IDL=1152 with the macro there is a huge difference DL = 3590 IDL = 2299

    I believe the difference comes from the If Range line. Once the VLOOKUP is performed all My DL's are set and there is no more option but to change the #N/A's in Column L to IDL... If the vlookup did not find a specific person on the list they are automatically IDL employees...

  6. #6
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    That's what I was saying about step 4. Step 5 completely overwrites step 4, so that it doesn't matter what the VLOOKUP said. Did you mean step 5 to be only for #N/A values in column L?

  7. #7
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Yes only the N/A values in columns L should be IDL

  8. #8
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    This code should do that:
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    First and foremost thanks for the help with the previous code. I just had a brief question what code could I use to have the code do the same process on three other worksheets at the same time? I have a workbook where the code needs to do the same thing on three sheets exactly the same way but on different columns. The three sheets names are "Temp Activos" "Temp JA" and "Temp Fit" they already exist on my workbook but when I tried to delete the first few lines of the code and start on the
    Please Login or Register  to view this content.
    line it gave me an error...

  10. #10
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    If you made you rearrange your columns so that it's the same column on each sheet, you can use a loop.
    Please Login or Register  to view this content.
    Otherwise, you'll just have to copy over the code, and change the sheet and column that you're using. I'm not sure what error you're getting, but that line is telling it to filter the used range of which sheet is in the With statement for rows that have "INATIVE" in column 9. So which sheet you're on, and make sure you have 9 columns, and check if there are any entries that say "INATIVE" in the 9th column.

  11. #11
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Ok I see but there will be times when we wont have any "INATIVE" employees at all so what would happen in that case? Here is how I tried to adapt the code to my workbook:

    Please Login or Register  to view this content.

  12. #12
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    If it causes an error when there are no INATIVE employees, you can just put that step in an If function, like
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Get Compile Error "Invalid or unqualified Reference" pointing to this line
    Please Login or Register  to view this content.
    and highlights this part in particular
    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    You probably are using it outside of the proper With statement. Sheets have used ranges, so it should be inside a With Sheets(#) statement.

  15. #15
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Fixed that issue... Now get "Application-defined or object defined error" pointing to this line in the code:
    Please Login or Register  to view this content.

  16. #16
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    I didn't get any of these errors running the code on your sample. If you haven't changed anything, I have no idea why you're getting them. If you have, then post the workbook so I can see the whole thing in context.

  17. #17
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Here is the workbook I am using it on... Its Module 2... I have also attached the whole file as the macros first module imports sheets from other workbooks...
    Last edited by Leith Ross; 10-23-2019 at 08:42 PM.

  18. #18
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    The second file just contains a shortcut that doesn't work for me. The only error I can get is "no cells found." To fix that, I can just add a If Not _ is Nothing statement, or I can put "On Error Resume Next" before it and "On Error GoTo 0" afterwards. I haven't been able to get the error you're having. Looking at your code, you could make it more concise and easier to edit by using a loop to go through the sheets. You can either refer to sheets by their number "Sheets(3)" instead of "Sheets("Regulares")" or you can create an array of sheet names to use like this:
    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    So basically I would add the Array to the beginning of the code? Which one of the two options (If Not or On error)would work better in case there is no INATIVEs in the column on a specific month?

  20. #20
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    Yes, you'd add the array at the beginning. It makes your code easier to edit because if you only have to make changes to one section, instead of going through each section to make a change. Either one would work. On error just tells the code to ignore the error and keep going, which is why you set it back afterwards to pay attention to errors. If not checks if nothing is there. On error will make it work even if there is some other error, like it can't figure out what object you're referencing, while If not will only fix the "No cells found" error. So it depends if you want to know about other errors, with that line, or just keep going.

  21. #21
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    Here are some general VBA tips. I'm not an expert, so they are just what I've picked up along the way.

    Here are my general tips:
    1. Learn how to debug and use the immediate window. It makes understanding code a lot easier
    2. Avoid using select/activate/selection. Instead of selecting something and then performing an operation on the selection, just perform the operation on that thing itself. It saves a step and removes a potential error (the wrong thing being selected because of user input or code changes)
    3. If you ever find yourself typing the same basic code again, or copying and pasting code, ask yourself if you can use a loop instead.
    4. Reading and especially writing to cells is slow. Do not write inside a loop. Instead dump your data into an array, change the array, and the write the array all at once. For some reason, writing 100,000 cells at once is faster than writing 1000 cells one at a time.
    5. If you're going to reference an object more than once or twice in a row, use a With statement.
    6. Using current region or usedrange can help you avoid copying tons of unused cells.
    7. Just for you, I'm noticing a lot of Set statements. You don't need those. Set statements are good at the beginning of the code to save the path for an object that you'll be using a lot. If you're setting something and then resetting it as something else, you're probably better off just eliminating the middle man.

  22. #22
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Thanks for the continued support... I have attempted to add the On error function and still get the No Cells found error... Actually I believe using the If Not would be better as it will still point out any other errors... Where would that go?

    Please Login or Register  to view this content.
    Last edited by Will03; 06-19-2014 at 12:18 PM.

  23. #23
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Actually I used this and its working, but let me also try your answer as well just to see:

    Please Login or Register  to view this content.

  25. #25
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    Putting the "On Error GoTo 0" so far down is kind dangerous. I'd put it right after the delete. Also, I'd put the "On error resume next" after the autofilter. What you don't want happening is it tries to auto-filter, fails, and then moves on to the delete and deletes all your data, since it's all visible.

  26. #26
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Like this?

    Please Login or Register  to view this content.

  27. #27
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    Yes. That or the If method should work.

  28. #28
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Code to make changes to a sheet

    Wonderful... k64 I would really like to commend you on the excellent service here, it has been a great learning experience for me and really appreciate you taking the time to answer the threads posted... THANKS... until next time...

  29. #29
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Code to make changes to a sheet

    Thank you Will03. Feel free to leave me a +Rep, and best of luck with your coding

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Make a Sheet Active from Code
    By rau in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-02-2014, 01:25 AM
  2. how do I make this code start at the first hidden row in the sheet?
    By Willardio in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2012, 06:24 PM
  3. make my code effect on more then 1 sheet
    By moshelog in forum Excel General
    Replies: 1
    Last Post: 05-01-2012, 02:47 AM
  4. How do I make Sheet Code available in all sheets
    By antonf in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-20-2009, 04:31 PM
  5. [SOLVED] Code to make a sheet active
    By hshayh0rn in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-05-2006, 06:30 PM

Tags for this Thread

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