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

Excel and self generating purchase order numbers

I’m trying to figure out if it’s possible to create a formula that creates a new purchase order number every time I open up my purchase order form on excel. The current cell the number goes into is E5. My hope is that by doing this it will eliminate duplicate numbers. They would start at […]

c# – open excel workbook with different working directory

I am trying to open an Excel workbook from the temp directory, but specify an alternative working directory so that when the user goes to save the workbook the default save location is not the temp folder. Here’s what I am trying Process.Start(“EXCEL.EXE”, string.Format(“\”{0}\” /p \”{1}\””, workbookPath, desktopPath); Excel opens the workbook, but the /p […]

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

Overcoming compile error “else without if” in my code

This question already has an answer here: VBA code shows “Else without if error” 2 answers

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

Excel Formula Auto-Update When Columns Added

I have two formulas, (currently) one in cell O2 and one in cell P2 which do not auto-update when I add a column (next week they will be in P2 and Q2). For example, when if I right click on column O2 and say add column, the formulas are pushed to P2 and Q2, and […]

Import excel file with bad column order

I’ve to import a file (probably .csv or .txt) via a SSIS-package. The problem is, that the file is an excel file at the moment and is written very human readable which means it looks something like this: Name1; ColumnName1; Data; Data; Data;… Name1; ColumnName2; Data; Data; Data;… Name1; ColumnName3; Data; Data; Data;… Name2; ColumnName1; […]

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