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.

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.

Sub OpenTxtFile()

Dim TextFileName As String

PathSep = Application.PathSeparator
TextFileName = ActiveWorkbook.Path & PathSep & "zReports" & PathSep & "ExportedData.txt"

Workbooks.OpenText FileName:=TextFileName _
, Origin:=xlMacintosh, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8 _
, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1))


'rest of the code copies the data into a workbook

End Sub

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

Well don't I feel stupid

Thanks!

Either would work. What I need to do is convert a string such as "2435678.19237" to 2435678.19

Looking for an alternative vba function for the function CDec which doesn't exist in Excel for Mac.

Any ideas?

The error message I'm getting is "Method "Close" of object' _Workbook' failed".

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.

Public Sub ProgressList(StepProg As Single, StepDesc As String)
    ufAuditFormProgList.Controls("Label" & StepProg).Caption = StepDesc
    DoEvents
End Sub

Public Sub ProgLogStat(StepLog As Single)
    ufSetupLogProgList.Controls("CheckBox" & StepLog).Value = True    
        For T = 1 To 5000   'adds a pause between steps
        Next T
    DoEvents
End Sub

Sub CloseTextFiles()
Call ProgressList(StepProg, "Closing Text Files")
    Application.DisplayAlerts = False
    Workbooks("zSubK Amounts.txt").Close
    Workbooks("zExported PFR.txt").Close
    Workbooks("zECI_SubKs.txt").Close
    Workbooks("zChgItems.txt").Close
    Workbooks("Job_info.txt").Close
'update progress indicator check box
    Call ProgressStat(StepProg)
    StepProg = StepProg + 1
End Sub

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

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.

Function PremiumValue(TotalAmount, DocumentType)
    If DocumentType = "TypeA" Or DocumentType = "TypeB" Or DocumentType = "TypeC" Then
    Else
        If TotalAmount < 500000 Then
            PremiumValue = TotalAmount * 0.015
        Else
            If TotalAmount <= 2000000 Then
                PremiumValue = ((TotalAmount - 500000) * 0.01195) + 7500
            Else
                PremiumValue = ((TotalAmount - 2000000) * 0.00875) + 25425
            End If
        End If
    End If
End Function

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

Thanks!

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?

Sub DeleteColumns()


Dim x As Integer
Dim TotalCount As Integer


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


'set counter to number of total columns
    TotalCount = Range("ColCount").Value


For x = 1 To TotalCount


    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(0, 1).Range("A1").Select
    
Next x


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub