Tag: vba

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. […]

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 […]

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 […]

Add event listeners to procedurally generated controls without using a user form

I have a spreadsheet and create ListBox controls in every cell of a column. I’m trying to capture their selected contents but the examples of capturing events on runtime generated controls all involve using a user form and I’m not using one. I’m new to VBA so how can I reproduce the code below from […]

Separate complex text from number patterns from a single string

I have a text file that looks something like this (but with hundreds of lines): 1147-1 SYRUP: DR.PEPPER 5GALLON/BOX 1653-1 SYRUP: DIET DR.PEPPER 5GAL/BOX 2011-2 WATER DISTILLED 6 / 1 GA 1217-2 ALL PURPOSE RASPBERRY FIL 40# 1273-1 STRAWBERRY PIE FILLING 38# 2893-1 BREAD: SOURDOUGH 12/1# OVAL 2287-1 BREAD SQUAW: 8/2.25#LF 1929-1 VINEGAR HERB CONT […]

How to set scrollbar value property in VBA

I do have a scrollbar and an input field (tempBuiten). I like the scrollbar to follow the value in the input field. So if a user set a value in that field, I would like the scrollbar to move to that position. This is what I have so far: Private Sub Worksheet_Change(ByVal Target As Range) […]

Select on .csv file stops working after columns 1 and 2

I have the following line in a .csv file: 1234;Einkauf;26.08.2015;137873;616005;50308;Hiag;4100000;Place Rd. 8, 9999 Ort, HST;;;Test Place Rd. Ort;2000;VSM80;185.05;12;155.55;0.02 I run the following code: Set cn = CreateObject(“ADODB.Connection”) Set rs = CreateObject(“ADODB.Recordset”) cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strPath & “;Extended Properties=””text;HDR=Yes;FMT=Delimited””;” sQuery = vbNullString sQuery = sQuery & “SELECT * FROM ” & strFileName sQuery = sQuery […]

Need to add diagonal cells with an additional cell for each subsequent diagonal

I need help getting the sum of specific cells in a diagonal format. My issue is that each subsequent diagonal grows by 1 cell. I was thinking I use a For loop, but I don’t know how to add an additional cell to the summation for each iteration of the loop. The picture below shows […]

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