Tag: excel-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 […]

Excel Combobox filter for partial matches

I have a spreadsheet to track some expenses. Since the number of rows can get fairly long, I decided to include a UserForm, in which the user can simply input the date, the amount and a category. The categories can be chosen from a Combobox, which is populated as the UserForm is initialized. The problem […]

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

Code to Combine Sheets Does Not Finish Combining

The code below is intended to take the data in columns B:N from several spreadsheets and combine that data into one sheet and then make a table from it. Column A gets the file name from the source files. The columns are titled as follows: Site/Scan Name Plugin ID CVE CVSS Risk Host Protocol Port […]

Excel VBA Setting ListView Scrollbar Position

I currently have a ListView in a userform that has sorting functionality built in. When the column header is clicked the ListView is sorted properly, but it refreshes the scrollbars back to the default positions. What I am looking for is a way to navigate back to whatever the horizontal and vertical scrollbar positions were […]

ListBox1.Selected(i) = True stops working

I want to move selected Items from one Listbox into a second Listbox , its working fine. After a while though, its stops working! Private Sub add_Click() ‘delete old selection For i = 0 To Listbox2.ListCount – 1 Listbox2.RemoveItem (i – counter) counter = counter + 1 Next i For i = 0 To Listbox1.ListCount […]

vba listbox to connect with excel

I am getting an error on below line. ListBox1.RowSource = “Tabelle1!A2:C” & loletzte Here is my code: myFileNameDir3 = Sheet3.Range(“V10”).Value & TextBox116.Text & “.xlsx” Workbooks.Open fileName:=myFileNameDir3, UpdateLinks:=0 Set ws3 = Worksheets(“Sheet1”) With ListBox5 .ColumnCount = 3 .ColumnWidths = “1cm;2cm;2cm” .ColumnHeads = True ListBox5.RowSource = ws1.Range(“A1”).CurrentRegion ‘oder: ‘ListBox1.RowSource = “Tabelle1!A2:C” & loletzte End With

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