VBA: insert max formula, referencing column of a closed workbook

First Post. I have a workbook that is a list of customer names, each name is a link to their individual workbook that is on a sharepoint site. The second column is a “Last contact” column which has a formula =MAX(‘https://example.com/folder/Folder/Customer Library/Area/T/[Customername.xlsm]Notes’!$B$2:$B$120) I am able to make this formula manually by opening the linked workbook, […]

Values imported from other workbook differ

I’m writing a VBA module in Excel to populate a worksheets with data from another worksheet. It imports the data through code along these lines: Src.Wd = Workbooks(WB_Src.Name).Sheets(“Quote Product Associated View”).Cells(Counter, SC_Wd).Value Workbooks(WB_Calc.Name).Worksheets(WS_CalcData.Name).Cells(Counter, 7).Value = Src.Wd Everything works fine with the import, except that the numbers in many cases differ slightly from the original data. […]

how can i read a file with extension .123 and how to convert it to Microsoft Excel

How can I read a file with extension .123 and how to convert it to Microsoft Excel or read it in MATLAB? I’ve tried Lotus-1-2-3 but it doesn’t work, and this site http://www.tech-faq.com/convert-lotus-123-to-excel.html.

Parse Large Excel file on with javascript

I want to parse large excel file with any javascript library to get worksheet names to allow user to choose worksheets that will be process on the server side. I see this library https://github.com/SheetJS/js-xls can parse excel file but will load all the file into memory and the client machine will freeze if file is […]

VBA For Excel Out of memory Error – How can I avoid this?

I am working on a VBA macro to compare some values between databases to update some default vs. recommended settings. I have 3 sheets in my workbook (AV, AK, and AP) with about 16,000, 660, and 9,000 rows respectively. Each sheet also has 6-9 columns used. I am getting an “Out of Memory” error when […]

PHPExcel generating corrupted file – ob_end_clean() not working

I’m trying to generate a .xlsx file using PHPExcel. The process is generating the file, but when I’m getting to open the file, It says It’s corrupted. When I use Open and Repair, the file opens and It’s ok, but I don’t want to repair everytime I generate the file. I tried to use the […]

Find and replace to prepend area code and exchange code to selected telephone numbers

I need to search a worksheet and find all instances of -nnnn where n is a number. I need to find just that pattern so 888-545-0909 would NOT match as it has to be just those five characters: a dash then four numbers. An example cell is like -5456. I need to then replace -nnnn […]

Excel SUMPRODUCT “greater if” #VALUE! error

I’m having an issue when using the SUMPRODUCT function to reference tables with “greater than” and “less than” in SUMIFS values. More specifically, the following gives me a “#VALUE!” error. =SUMPRODUCT(SUMIFS(Perf_Data[Gross Claims Paid (non-cumulative)],Perf_Data[Year (AY/UWY)],”<“&’OUTPUT – S.19.01′!$A24,Perf_Data[Development Year],”>=”&Perf_Data[Year (AY/UWY)]+15,Perf_Data[Applicable Line of Business],LoB_Selection),Perf_Data[@[Currency check and multiplier]]) The code above sums the gross claims paid, if their […]

Attach recipients using range from sheet

I have the following code which lets me prepare an email which is ready to be sent: Set OutApp = CreateObject(“Outlook.Application”) Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = ThisWorkbook.Sheets(“Users”).Range(“A1”).Value .CC = “” .BCC = “” .Importance = 2 .Subject = “[ACTION REQUIRED] Format(Date, “YYYYMMDD”)” .HTMLBody = “some_body” .Display End With […]

error when using an array to filter on sheet

I have a section of code that’s supposed to filter to show everything that’s not specific data and it’s giving me trouble: Sheets(“main”).Select Range(“A1”).Select Range(“A1:N1”).AutoFilter Field:=1, Criteria1:=Array(“<>PXC532”, “<>PXX338”, “<>PZR290”, “<>QBQ519”, “<>QBZ561”, “<>QCB023”, “<>QCF365”, “<>QCF386”, “<>QCG343″), Operator:=xlFilterValues it’s highlighting “Range(“A1:N1″)…” and stating autofilter method of range class failed, think it’s just something I’m overlooking but have […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.