Posts by davec0864

    I have a table of data that I summarize by date (Column B) and by "Month Ending" label (Column A) in columns to the right of the data. The dates in the table are dynamic, so the "Month Ending" label will change positions depending on another variable, so I can't use the Subtotal function; the subtotal row will be in different locations. I'm using IF formulas to check for the "Month Ending" label related to the row the IF formula is in. Example formula in Cell K24:



    =IF(A24="Month Ending",I24,

    [if the current row 24 is the Month Ending row, then the value in Cell K24 is equal to the value in Cell I24]


    IF(A23="Month Ending",SUBTOTAL(9,K$2:K23),

    [if the previous row 23 is the Month Ending row, then the value in Cell K24 is the SUBTOTAL of all rows above Row 24]


    "")

    )


    I'm doing this in four columns (K thru N) with with the first IF formula in each column a different formula summarizing different values. This formula works fine until I copy it into the other cells in the column; then the subtotals are all 0.


    Any ideas?

    I'm wanting to automatically open another workbook using the BeforeClose event for the current workbook. I tried the following code and it works except the "current" work book doesn't close, and the file menu for the second workbook is deactivated so that workbook can't be closed.


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Answer1 = MsgBox("Update the Dashboard?", vbYesNo)
    
        If Answer1 = vbYes Then
            Workbooks.Open FileName:= "path to filename/filename.xlsx"
        End If
    
    End Sub

    I'm sure I'm just missing something simple.


    Thanks for your help.

    Dave

    Excel 2019 for Mac no longer supports "Workbooks.OpenText Filename". I've Googled the issue, and all of the posts I've found are for "GetOpenFilename" which doesn't get me where I want to be. My current code opens a specific file in a specific location.


    This code works fine in Excel 2011 for Mac, but does not work in Excel 2109 for Mac.


    I need help with code that will work in Excel 2019 for Mac.


    Thanks

    I've been using code in a worksheet several times every day for over a year and it has always worked until I made what I thought was a simple change. Now the code gives me a "Method "Close" of object '_Workbook' failed". I've checked to make sure I have DoEvents and Application.DisplayAlerts = False at all the appropriate locations within the code.


    My main code opens text files and copies the data into the main workbook into various worksheets within the main workbook, then closes the text files. I have a userform as a progress indicator that displays a label and check box as each step of the code is completed. But I wasn't using the progress indicator beyond a specific location in the code. I wanted to expand the progress indicator to the balance of the code. I didn't change anything except to copy the progress indicator call routine into the various places within the code. Now Workbooks.Close and Workbooks.Activate give the error message, but only from the point where I recently inserted the progress indicator call routine. Everything prior to that location in the code works just as it previously did.


    I know it has to be something simple, but I’m missing it. Here’s a sample of my code.


    Sorry I guess I was a little too obtuse. I have the variable declared as a public variable, but how do I define or set the variable value so every routine can use the workbook name value regardless of the order the routines are executed. i.e., without redefining the variable every time I launch any routine, in any order.


    I have one main routine that calls several other subroutines and it passes the workbook name variable to each of the subroutines it calls, and that works fine. But each of the subroutines can be called individually outside of the main routine. In order to do that I have to redefine the work name variable in each subroutine, and that works for executing each subroutine individually. But once I relaunch the main routine, the subroutines lose the value of the variable as it is being passed to them; i.e., the variable name is being redefined within each subroutine. And somewhere along the line the value gets set to null.


    The subroutines open other workbooks and copy data into the main workbook in a separate sheet for each separate data file workbook. The subroutines need to be able to activate the main workbook to continue execution which includes arranging, sorting, subtotaling, and formatting the data, then closing the data file workbook(s).


    All routines are in the same module. I'm using the following code


    Code
    Public myWbk As String   'in the Declarations section of Module1
    
    
    myWbk = ActiveWorkbook.Name   'in each routine
    
    
    Workbooks(myWbk).Activate   'in each subroutine to get back to the main workbook after opening a data file workbook


    I'm certain I'm just doing something out of order and/or in the wrong location(s).

    I have a template workbook that is copied and renamed to be unique for the project and date the report is created for. The routines in the workbook modules need the name of the primary workbook as a global variable so the routines can always get back to origination point that calls each routine.


    Is there a simple way to declare and define a global variable that is the workbook name so all modules and routines in the workbook always know the workbook name.

    I have over a dozen labels in my userform and I would like to apply this solution to the values displayed in each label. All the labels are all the same size and the values are always one or two digit numerical values. The label names are unique for each label and are descriptive of the function of the label within the userform. Example label names are lbTHRP1, lbTHRP1a, lbAMod1, lbRV2a, lbTotRV2, etc. There are command buttons within the userform that have code that sets the caption value for the label(s) associated with the code called by each command button click event. The code for each command button click event has nested IF THEN statements so typing in the vertical alignment code and making changes to the code for every instance of a command button click and the subsequent conditional values for the caption value is very tedious.


    Is there a way to call the solution in this previous post as a subroutine with a variable for the label name?

    Have a custom function that has two (2) variables, but also has seven (7) fixed parameters that could change depending on the scope of the project, but any change would be rare. There can be up to 700 cells in the worksheet with this function. To minimize recalc time I would prefer to "set" those parameters not as variables passed to the function, but rather as "fixed" values that are set in the worksheet. Below is the code of the function.


    TotalAmount is an integer variable, and DocumentType is a string variable. The numerical values in the function are the values that could change depending on the scope of the project.



    Is there a way to update fixed parameters in a function? Also would appreciate any thoughts on more efficient code for the function.

    I have a project that has a few user forms, several modules, and approximately 30 macros/subroutines. In the Project window I have named the modules so they are listed generally in the order the macros are executed; see attached screenshot. But there's no easy way to locate a specific macro without adding more modules or using the Tools : Macros menu. Anyone have any ideas on more effective ways to organize large projects so macros are easier to locate and modify within the editor? I'm using Excel 2011 for Mac.

    I need to delete every other column in my worksheet that has 8,192 columns of data. The macro below works but takes several minutes to execute. Any suggestions on how to speed up the macro?