Posts by Max1616

  • VBA - if cell value true, offset, check value then delete if criteria met

    Re: VBA - if cell value true, offset, check value then delete if criteria met


    *Edit to the last post in the "Right" Example of an Or statement
    1.Try to avoid using active cells or selecting cells if you can. In your particular case, the code wasn't activating any cells, so the active cell was always the most recent cell selected by you before you ran the macro.
    2. If you use "Or" inside an "If" statement, you need to completely restate the argument.


    Wrong:

    Code
    If ActiveCell.Value = "ENROUTE" Or "AVAIL" Then


    Right

    Code
    If ActiveCell.Value = "ENROUTE" Or ActiveCell.Value = "AVAIL" Then


    Keep in mind that in the actual code I provided in the previous response, I removed all instances of "activecell"


    I hope this helps!


    Sincerely,
    Max

  • VBA - if cell value true, offset, check value then delete if criteria met

    Re: VBA - if cell value true, offset, check value then delete if criteria met


    1.Try to avoid using active cells or selecting cells if you can. In your particular case, the code wasn't activating any cells, so the active cell was always the most recent cell selected by you before you ran the macro.
    2. If you use "Or" inside an "If" statement, you need to completely restate the argument.


    Wrong:

    Code
    If ActiveCell.Value = "ENROUTE" Or "AVAIL" Then


    Right

    Code
    If ActiveCell.Value = "ENROUTE" Or ActiveCell.Value = "ENROUTE" = "AVAIL" Then


    Keep in mind that in the actual code I provided in the previous response, I removed all instances of "activecell"


    I hope this helps!


    Sincerely,
    Max

  • VBA - if cell value true, offset, check value then delete if criteria met

    Re: VBA - if cell value true, offset, check value then delete if criteria met


    Try this:

  • Find Date in Sheet

    Re: Find Date in Sheet


    I can't recreate your problem, the code is working fine on the example workbook I created. Can you attach your workbook for reference?

  • Find Date in Sheet

    Re: Find Date in Sheet


    I just read your response, try this:


  • Find Date in Sheet

    Re: Find Date in Sheet


    There are 3 reasons that I can think of off the top of my head which could cause this:
    1. Your userform is pointing to the wrong spreadsheet
    2. The date is not present on the spreadsheet you are evaluating
    3. The data type of the date in your spreadsheet is not being recognized as a date (It's being read as a text field)


    What is the number format of the date in your spreadsheet (Date/General/Text/etc.), and what does the date actually read within the spreadsheet (6/20/2017, 06/20/2017, June 20 2017, etc). This would be a lot easier to trouble shoot if you were able to provide an example spreadsheet


    [sw]*[/sw]

  • Find Date in Sheet

    Re: Find Date in Sheet


    Try this:



    Seems like you had some problems with the date data types matching up in the lookup - For this I created a data variable (instead of string) so excel is storing the date as it's numeric value and not text.
    I was also having some issues with accomplishing your goal using the range variables, I changed some stuff around there too.


    Let me know if you have any questions.


    Sincerely,
    Max

  • WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

    Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget


    Hi Jan,


    I'm glad you find this site helpful!


    I think I understand what you are trying to accomplish. Try this:



    Sincerely,
    Max

  • WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

    Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget


    Hello jan.g!


    Try this:


    I think this is what you are looking for, let me know if you have any questions!


    Sincerely,
    Max


    Edit: Quick change to the code

  • Copy/Paste Text, then Merge & Left Justify Column Range

    Re: Copy/Paste Text, then Merge & Left Justify Column Range


    Hello! I rewrote most of your code to run more efficient. General rule of thumb when writing VBA: 99% of the time you should be able to complete your task without "Selecting" any objects. Also the trick to find the last row in a data set is very useful and makes your code run a bit faster:



    Let me know if you have any questions!


    Sincerely,
    Max

  • Macro: Auto Generate Custom Serial Number

    Re: Macro: Auto Generate Custom Serial Number


    Hello!


    I played around with this for a little bit and I think I have a solution for you. Place this code in the Worksheet Module Sheet1(Form):


    It's storing the last used serial number value for both JO and WA. This code will populate the follow-up sheet once the form is completely filled out. Then it will automatically clear the form.


    Let me know how it works!


    Sincerely,
    Max

  • Linking Excel Files Read Only

    Re: Linking Excel Files Read Only


    This is how you can link the data as read only:


    Data Tab-> From Other Sources -> From Microsoft Query -> Double click "Excel Files*" -> In the directory window (Right window) find the file path of your excel sheet (This is kind of a pain since the window is so small) -> Once the path is chosen, in the Database Name window (Left window), Select your excel workbook with the source data -> Check the "read only" box under the help button -> Click ok -> Select the sheet that contains your source data and click the arrow to move it into the window "Columns in your query:" -> Click Next 3 times -> Then Finish


    Let me know if you have any problems here!


    Sincerely,
    Max

  • Compile Particular Columns from Different Workbooks with Options

    Re: Compile Particular Columns from Different Workbooks with Options


    I think I fixed all the errors. I played around with it for a while and was able to add and remove items successfully. Also I am now having the remove/add combobox lists update everytime something is added or removed:



    Let me know how this does!


    Thanks!

  • Compile Particular Columns from Different Workbooks with Options

    Re: Compile Particular Columns from Different Workbooks with Options


    Easy enough to fix! For your first problem:
    "Everything is fine except that I can add columns, but can't remove them with having "out of range" error."

    I have a solution for the error that is occurring, but first I wanted to clarify what Commandbutton2 does. Command button two should say "Close" or "Cancel". It is not meant for removing columns. Commandbutton1 will take care of both removing and adding columns. If the remove combo box is populated, then the remove code will kick in.


    That being said, here is the new code:


    It now will update the existing columns with this line of code (*This update happens when the user form is called, not when the workbook is opened):

    Code
    'Loops through each exisiting column to update them:
For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
    Data = Cells(1, i)
    wbname = Cells(WorksheetFunction.Match(Data, Columns(30), 0), 31)
    Workbooks(wbname).Activate
    Columns(WorksheetFunction.Match(Data, Rows(1), 0)).Copy
    wbFinal.Activate
    Cells(1, i).PasteSpecial Paste:=xlPasteValues
Next i


    Let me know how it works!


    Sincerely,
    Max

  • Compile Particular Columns from Different Workbooks with Options

    Re: Compile Particular Columns from Different Workbooks with Options


    Hello again onexc,


    It took me a while to put all this together, I hope it works for you!


    This will require 4 controls on your userform:
    CommandButton1 - Used to add or remove columns from your final workbook
    CommandButton2 - Used to close/cancel the userform
    Combobox1 - Used to indicate what columns you would like to add
    Combobox2 - Used to indicate what columns you would like to remove


    Once done, place this code in the userform module:


    This is quite a bit of code, so I annotated it the best I could that way you can understand whats going on.
    Main things to note:


    1. I am using columns 30-35 in your final workbook to hold some values. If you need these columns I can update the code to accomadate.
    2. You will need to update the file path where the workbooks live, and the extensions of your workbooks (non-final) if it is not already .xlsx.
    3. I named the sheets the same as what you attached, update if neccessary.
    4. Make sure control names match what I have.


    Here are the bits of code that may need your attention (Right at the top of the initialize sub:

    Code
    'Variables for excel sheet names (Update these if necessary)
wb1Name = "Workbook_for_1st_Data-1"
wb2Name = "Workbook_for_2nd_Data-1"
wb3Name = "Workbook_for_3rd_Data"


FilePath = "Z:" 'File location of excel files
FileExtension = ".xlsx" 'File extension of excel files


    I hope this works! Let me know what you think!


    Sincerely,
    Max