User Profile
Brad_Yundt
MVP
Joined Jul 11, 2019
User Widgets
Recent Discussions
Re: Need to copy some data from workbook A to Workbook B
Bob, Recorded macros are littered with .Select and .Activate statements. You don't need them, and getting rid of them will make your macro shorter, easier to debug, and faster. You will notice that I rewrote your code to use With blocks. When you use an object in a With block, any time you start an expression with a dot, VBA knows you are referring to the With block object. And because VBA doesn't have to resolve a fully qualified reference, the code runs faster. When you ask for help on Excel help forums, you should anticipate almost everybody either getting rid of the .Select statements for you, or wishing that you would get rid of them yourself. The one concern I have is that after I eliminated your .Select statements, your code calls the sub ShowPreviousEntry. I don't know which worksheet needs to be active when you run that sub--but I may have broken a dependency. Ideally, all of your code will be rewritten to get rid of the .Select statements. You may need to learn a little bit of programming from scratch, but I have found most people pick it up with about an hour of study or one on one tutoring. Brad Sub PostAddr() ' ' PostAddr Macro ' Post entry data to data sheet ' ' Keyboard Shortcut: Ctrl+Shft+A ' Dim mySpace As String Dim longAddress As String Dim addrLen As Integer Application.ScreenUpdating = False If CheckForDuplicateAddress = True Then Exit Sub mySpace = " " ' check to make sure there is data to post With ThisWorkbook.Worksheets("Entry") addrLen = Len(Trim(.Range("B9"))) End With If addrLen < 5 Then Call ShowPreviousEntry 'Does this sub depend on a particular worksheet being active? Exit Sub End If ' insert a blank row pushing existing rows down Set wsData = Workbooks(dataName).Worksheets("Data") With wsData Set rgData = .Rows("4:4") rgData.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove '..... End With End SubRe: Request for Experts Formulas for this Calculation
Superhero3984 The figures in your example table neither round up to the dollar nor to 10 cents. So I don't understand how to interpret "I required to derive to a nearest 0.10 cent if its above 0.5 should be 1.0 then." Either revise your example table, or clarify how you want the calculations to be done.Re: Trying to create an "if statement" in excel
Carol423 Let's suppose the total amount of time worked is in cell A1, and the amount of time worked on job XYZ is in B1. Furthermore, time is being entered as decimal hours (e.g. 6.5) rather than clock time (6:30). I suggest using MAX instead of IF when you figure out your overtime and pro-rating. Once you wrap your head around it, MAX makes the calculations simpler. The number of hours of overtime is =MAX(0, A1-8) If you want to assign overtime pro-rata to project XYZ you might use =MAX(0, A1-8)*(B1/A1) If you use time and a half for overtime, the number of equivalent straight time hours is: =B1+0.5*MAX(0, A1-8)*(B1/A1) If you still need help, I suggest that you post a workbook in this thread using the Browse... button at the bottom of the Comment box. That way people can give suggestions specific to your worksheet layout.Re: Problemas con el Editor de Visual Basic en Excel 2016 para Mac
Jorge, You may display userforms in Mac Excel 2016, but you cannot create them or edit how they look. Neither can you change the properties of the UserForm or any objects on it using the Property window. You can, however, edit the code for those userforms. Until Microsoft developers get around to eliminating this gap, you should build your userforms using Windows Excel and then test whether they display properly on your Mac. I run Windows Excel on a Parallels Virtual Machine on my Mac, and have done so for years. Code runs at perhaps 75% of the speed of a Windows computer. If I am satisfied with how fast code runs on my virtual machine, I know that my customer will be happy, too. BradRe: Need to copy some data from workbook A to Workbook B
Bob, Excel VBA has a pre-defined set of so-called "event subs" that run whenever the event they are associated with occurs. Each of the worksheets and ThisWorkbook have their own set. In my copy of Excel, there are 48 in ThisWorkbook, which you can see by choosing Workbook in the left field and then clicking the field on the right to display the dropdown choices. Some event subs run before the associated activity is completed, while others run after. All of them are triggered automatically--you need do nothing more than include the stub (first and last lines of the sub, which Excel generates for you automatically) in the class module sheet. It is possible to have a sub named Workbook_Open in a regular module sheet. That sub won't run automatically when you open the workbook, and if you have another Workbook_Open sub in ThisWorkbook, you will need to qualify the sub names so VBA knows which one you mean, e.g. Module1.Workbook_Open. All that said, it isn't good practice to have a sub in a regular module sheet with the same name as an event sub. "All my code is in Module1." That's good practice. The snippet below shows how you might save and close a workbook. Workbooks(dataEntryName).Save. 'Save workbook Workbooks(dataEntryName).Close SaveChanges:= False. 'Close without saving Workbooks(dataEntryName).Close 'If changes made, Excel will ask to Save before closingRe: Problemas con el Editor de Visual Basic en Excel 2016 para Mac
Jorge, I was quite impressed with how well Bing Translate handled the translation of your Spanish inquiry into English. I hope that it does equally well for you in the other direction. Mac Excel 2016 (including VBA) was completely rewritten to use the Cocoa API instead of the older Carbon API. At initial release, the VBA IDE in Mac Excel 2016 was missing so many features you could do little more than debug code written in Windows Excel (or Mac Excel 2011). If you have Office 365, many of the missing features were restored--but not all of them. I have the Office Insider release of Mac Excel 2016/Office 365. It is more recent than your version, but the middle icon is still grayed out. On Windows Excel VBA, that icon returns you to the worksheet. In Mac Excel 2016 VBA, I click the yellow circle at the top left corner of the VBA window to minimize it and accomplish the same thing. Alternatively, if you leave a small portion of the worksheet window exposed, you can click on that to jump back to the worksheet. The far left icon in Mac Excel 2016 VBA does take you to the code for the selected object. This was a feature I had never noticed before in either Windows or Excel VBA. My habit is to doubleclick the gray module icon in the Project Explorer (shaded gray in screenshot below) instead.Re: Request for Experts Formulas for this Calculation
Superhero3984 I tried to build a formula that went from your Total column to each of the other two, but the results were inconsistent. The closest I got was to assume amounts should be rounded to the nearest 5 cents. The following formula assumes your sample data are in A2:D2. Values in D2 are given. The formulas for B2 and C2 (less 15% discount and plus 3% GST) are shown below: =MROUND(D2*0.85,0.05) =MROUND(D2*1.03,0.05)Re: Need to copy some data from workbook A to Workbook B
Bob Hilton FWIW, sub GetEntryFile can never fail to find the entry workbook--because it is the one containing the code that is running. Not a problem--just that you have a useless test of whether that file is open. The data file workbook, however, may or may not be open. I revised sub GetDataFile so it would open the workbook if need be: Public Sub GetDataFile() Dim dataFilePath As Variant Dim wbx As Workbook 'MsgBox ("Please select the file containing the data for Neighbors") dataFilePath = Application.GetOpenFilename _ ("Excel files (*.xls, *xlsx, *xlsm, *xlsb", Title:="Please select the file containing data for Neighbors") If dataFilePath = False Then Exit Sub dataName = Right(dataFilePath, Len(dataFilePath) - InStrRev(dataFilePath, _ Application.PathSeparator)) bDataOpen = IsWorkbookOpen(dataName) If Not bDataOpen Then Workbooks.Open dataFilePath 'MsgBox "Data workbook (file) failed to open", vbCritical, "Data Workbook Open Failed" End If ThisWorkbook.Worksheets("Entry").Range("E22").Value = dataFilePath End Sub
