Set Variable in Named Range (Loop)

I want to call named ranges with some kind of loop Lets assume i have sheet with: 2 named ranges 2 shapes [namedrange1] is set to A1 [namedrange2] is set to A2 Range A1 value is 1 Range A2 value is 2 first shape is named shape1 second shape is nam...
more »

2017-02-19 16:02 (0) Answers

VBA - Run Time Error Object Variable

I am running into a problem with the following code, specifically when hitting run I am getting the error message "Run Time Error 91 : Object Variable or With Block Variable Not Set". I have attached a picture of what line is highlighted in yellow....
more »

2017-02-19 16:02 (1) Answers

Passing range as function(arguments)

I am trying to pass a range as a parameter in a function so that I can select and clear it. However, I keep getting an Application defined/object defined error. If I do this in my clearData method where I just do range("m3").select instead of ran...
more »

2017-02-19 06:02 (2) Answers

Need help finding suspected error in subroutine

I am having difficulty finding a suspected error in my code for a project I am working on. I have build a table that keeps track of items I order online. It stores the name of the item and corresponding information (quantity ordered, pieces include...
more »

2017-02-19 03:02 (0) Answers

Transpose sections of an Excel column

I have 8000 rows of data in column A. I am trying to write code that would scan the rows and each time there's a cell formatted as bold, to determine a range that includes that cell and all cells in the subsequent rows until the next bold cell. This...
more »

2017-02-19 01:02 (1) Answers

Find non-identical text in two tables

Problem: I am using Excel 2010 VBA to find non-identical (very long) Error Texts for an identical structure element (e.g. "123_789") and an identical Error Code (e.g. "ER005") between 2 tables. If the result is non-identical set the background color ...
more »

2017-02-18 20:02 (1) Answers

Excel VBA costum formula too slow

I have this vba excel costum formula: '=ConcatenateRangeIfs(A1;Sheet2!C:C;B1;Sheet2!D:D;Sheet2!G:G;". ") Function ConcatenateRangeIfs( _ ByVal match_val1 As String, _ ByVal match_range1 As Range, _ ByVal match_val2 As String, _ ByVal...
more »

2017-02-18 20:02 (1) Answers

Count specific files in folder with excel vba

I need some help with my excel vba. First of all let me tell what it should do... On a network folder there are pdf-files which should be count. Folders look like this: X:/Tests/Manufact/Prod_1/Machine/Num/Year/Month/TEST_DDMMYYYY_TIMESTAMP.PDF ...
more »

2017-02-18 00:02 (3) Answers

Removing All Spaces in String

I created a macro for removing all whitespace in a string, specifically an email address. However it only removes about 95% of the whitespace, and leaves a few. My code: Sub NoSpaces() Dim w As Range For Each w In Selection.Cells w...
more »

2017-02-17 20:02 (5) Answers

VBA Lookup in Another Workbook

I am having an vba problem with Excel. So I have this workbook, "Book Tool - Updated Feb. 2017.xlsb", that I am currently updating and will distribute to about 10 team members to use to keep track of their work. What I am trying to do is lookup data ...
more »

2017-02-17 18:02 (1) Answers

Pass multiple entries between Userforms on VBA

So I am trying to a create userform. I currently have two forms, the first is general data for a truck, once the user has entered all the appropriate text box's the second userform will display and transfer the data onto the Truck Info label for refe...
more »

2017-02-17 17:02 (1) Answers

combine two or more string arrays

I'm trying to find the fastest way to concatenate row values from about 7 columns, from huge files(800 000 rows). I found an almost instant way of loading each column in separate arrays. But when I use the code below, the processing is very slow, l...
more »

2017-02-17 16:02 (1) Answers

Extracting data from pivot table vba

I have a pivot table to aggregate "coverage" on "part" only for accepted parts. I want then to extract the "sum of coverage" to another sheet. I wrote the following macro: Sub Pull_data() 'Update the pivot table Sheets("Pivot").PivotTables("Pivot...
more »

2017-02-17 14:02 (2) Answers

VBA Filter by 2 months ago

I'm trying to filter by 2 months ago (i.e. Dec 2016) and doesn't work. But, if I go in excel select to edit current filter and press ok, it will show right results. What is the issue? My code: With Worksheets("SER Common") .Range("A1").AutoFilt...
more »

2017-02-17 14:02 (2) Answers

VBA Creating hyperlink to another dynamic workbook

I am struggling to find a solution for my problem: I have a list of items on one workbook and a macro that creates a sheet for each item on different spreadsheets. Each Code in Column A has the type of product as the first Letter and each product ...
more »

2017-02-17 13:02 (2) Answers

VBA text to columns with "" delimiters

I will be quick. I have a variable 'strLine' with text: The exact string will look like that: "Text1","Text2","Text3","Text4","Text5" So, delimiter in my case is: " How I can extract text and write it in columns. Expecting results in cells: A1...
more »

2017-02-17 09:02 (3) Answers

Copying PivotTable Values in VBA

I have created a pivot table via macro and want to copy the pivot table as values using VBA. Creating a Pivot Table went well but copying it to another sheet gives me a headache. Here's the code: Sub test() Dim shtTarget, pvtSht As Worksheet Dim pc ...
more »

2017-02-17 06:02 (1) Answers

Close a modal opened by vba macro with c#

I'm creating a c# program that executes a vba macro in an excel sheet. After the vba macro finishes its execution, it displays a modal window, that does not allow to continue with the execution of my program. Is there any way how I can close that m...
more »

2017-02-16 23:02 (1) Answers

Excel VBA - Macro Runs but has no effect

I'm new to Excel VBA and this is my first macro, so please forgive me if I've made a very obvious mistake. I have the following code to compare to worksheets and, if a match is found, to make a note on one of the sheets. It runs with no errors, but...
more »

2017-02-16 22:02 (1) Answers

VBA Code for drop-down list with dynamic range

I am trying to write a macro for multiple drop-downs in "n" cells (let's say 100) in a column. The ranges(drop-down values) for these drop-downs have to be picked from a table with same number of rows (100 in our case). I am unable to run the for lo...
more »

2017-02-16 19:02 (1) Answers

Variable range in Visual Basic for Excel

I want to define a variable range in an Excel macro with VBA. The first cell is always A25, but the last cell is moving depending on the number of data collected. This can be E35, or E58, etc. Any idea how to do this? ...
more »

2017-02-16 18:02 (3) Answers