天道酬勤,学无止境

excel-2011

VBA - Search and remove duplicates

Question I'm looking for an algorithm for which I do not have the VBA knowledge to script myself. So I'm stuck. It isn't through lack of effort trying because I have given it a go (plus, this bit of code is the last remaining piece of my bigger VBA code) I simply lack the knowledge/experience/skill... Basically, I have an Excel file. In this file is a sheet, "sheet1". Sheet1 contains many rows of data. The number of rows contained in sheet1 can vary from 1 to n. Sometimes, I may have 50 while other times I may have 30, etc. What is consistent is the layout of the book, i.e. I have codes in

2022-01-14 13:31:51    分类:技术分享    vba   excel   excel-2011

Load contents of CSV file to array without opening file [closed]

Question Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 5 years ago. Improve this question I have a requirement to collate over 6000 csv files into a single csv document. The current VBA process is: 1. Open individual CSV data file 2. Load contents of file to array based on number of rows 3. Close individual CSV file 4. Process array In order to improve efficiency of the code and processing, I was hoping there may be a method to load the data

2021-12-09 16:33:12    分类:技术分享    arrays   excel   vba   macos   excel-2011

Date for VBA not working in Excel 2011?

Question Here is my code, I am attempting, which works BTW on a PC, but not on a Mac, to run this code and have an excel sheet created, named, add a tab, change the color of said tabs, change the name of said tabs, and then transpose the data while maintaining the format of the cells and width and height of the cells to the new worksheet. This works, on a PC.... but when I get onto a Mac, it doesn't. I go into References, and this is what I see. I see Ref Edit Control, and Microsoft Scripting Runtime are missing. I disabled both, and the script gives me an error here still: wbBK2.SaveAs Dir &

2021-12-09 02:43:55    分类:技术分享    excel   vba   macos   excel-2011

How can I convert a range to a string (VBA)?

Question What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of the formatting as possible (i.e. it needs to look like a table or list, not just a string of characters). I've tried working with CStr(), as well as converting from a range to an array and then to a string, but I just get errors. Edit: Code attempt Dim email_answer As Integer email_answer = MsgBox("Do you want to be emailled a copy of this schedule?", vbYesNo) If email_answer = vbYes Then Dim wb As

2021-11-22 09:41:56    分类:技术分享    vba   macos   excel   excel-2011

Excel SUMIFS of cells based upon multiple criteria

Question I'm trying to create a formula which calculates the sum an array of fields based upon multiple criteria. This relates to various franchises and the promotions which they run during the year My document contains two sheets with the following information within in it - 'Daily Revenues'!A:A - date. There is one entry per franchise per day with the revenue for that specific day. 'Daily Revenues'!B:B - the franchise name 'Daily Revenues'!D:D - number. The daily revenue for that day. 'Promotions'!I:I - date. The start date of the promotion 'Promotions'!J:J - date. The end date of the

2021-11-21 22:28:27    分类:技术分享    excel   excel-formula   excel-2011

Application.Filedialog support in Mac excel 2011

Question Couple of questions Is Application.Filedialog(msoFileDialogSaveAs) support in Mac excel 2011 vba? Difference between Application.Filedialog(msoFileDialogSaveAs) and Application.Dialogs(xlDialogSaveAs)? Answer1 I've just answered this question and your question here was referenced. To also support your questions: - Is Application.Filedialog(msoFileDialogSaveAs) support in Mac excel 2011 vba? No it is not, however there is a built in function in Mac VBA called MacScript, which you pass applescript through to get a value back, see my previously linked thread for more detail on this

2021-11-21 00:18:14    分类:技术分享    macos   vba   excel-2011

Excel 2011 - How to change output of textJoin from value of cell, to the cell name, so I can put it in Sum() to add those values

Question I'm using the textJoin UDF listed below to combine values from various rows into a single cell; it displays each value. However, I want to know if I can manipulate the output of this UDF so that instead of simply displaying the values I can add the values and get the sum of the values. Or ideally, if I can just modify variables to instruct it to ADD the values. Does anyone know if this UDF (which I didn't create) can be instructed to output the Cell names (A2, B2, C2), and if so can I put that output within the Sum() function so that it will add A2+B2+C2? Function TEXTJOIN(delim As

2021-11-10 19:13:13    分类:技术分享    excel   vba   macos   excel-2011   excel-udf

Display the upcoming due date

Question I am working with Microsoft Excel for Mac 2011 doing some personal finance and trying to devise a formula to display a specific date. I have a credit card bill that is due on the 24th of every month. I have the name in Column A, and the Date it is due in Column B. Say that the current month is October, and the bill will be due on the 24th, I want it to display 10/24/15 (mm/dd/yy). I do not want to show any previous dates or current date, I only want to display the upcoming due date, and I want it to remain set on 10/24/15 until 10/25/15 where it will show me the next due date as 11/24

2021-10-24 06:01:38    分类:技术分享    excel   datetime   excel-formula   excel-2011

Sumif between two dates over two columns

Question I would like to sum a column between two dates, however the dates are in separate columns. I want the sum total between the following dates 01-01-2014 (cell E1) and 31-03-2014 (cell F1). If you look at cell C6 the date is lower than the first range and is being included as you would expect. The current formula looks at the two columns individually i.e. is Start date (column B) above E1 and then is End date (column C) below F1. I need a formula that takes into account both dates i.e if start date is above E1 and below F1 then sum the Price column. The date at C6 is something I would

2021-10-19 12:16:14    分类:技术分享    excel   date   excel-formula   excel-2011

VLOOKUP not working in excel for MAC 2011

Question I have a problem when using VLOOKUP in excel for MAC 2011. I am looking up for a value two column in different sheet, Column A is the one I want to check, Column B is the reference. =VLOOKUP(A1, Sheet2!$A$1:$B$1000,2,0) Some of boxes in Column A get a value return, but the other cannot. For those which cannot, when I retype the words inside the box, and I get the value returned. I try to copy the Column A and selectively paste with value only, but those cannot get a returned value remained the same. I guest there is something wrong with the format, but I cannot fix it. Please help

2021-10-10 03:08:52    分类:技术分享    excel-2011