FAQ Database Discussion Community

## If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

## Replace reference with its value in Excel VBA workbook

vba,excel-vba
I have an Excel workbook to manage my Delivery Notes, which creates another workbook with actual delivery note and stores it in the folder for me, then places new record in my main workbook so that the Delivery Number for the next delivery note can be increased by 1. My...

## Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups

excel,vba,excel-vba
I'm working in Excel 2013 to (programmatically) add a straight line connector between the lower right hand corner of a rectangle that is part of a grouped shape with the endpoint of a grouped series of line segments. As it stands, I can't even seem to do this manually on...

## Sort multiple columns of Excel in VBA given the top-left and lowest-right cell

excel,vba,excel-vba,sorting
I am trying to sort these three columns (Sort By Col-2) in excel using VBA. Top-left (Row number and Column number e.g. 1,1) and lowest-right cell (Row number and Column number e.g. 9,3) are known. Every cell contains the values of String type. Input: Col-1 Col-2 Col-3 P1 I1 XYZ...

## Extract All Named Ranges Into A Class

vba,excel-vba
I have a workbook with a very large amount of named ranges (well over 200). I really need a way to work quickly and easily with all of the named ranges so I can then work with / populate them using VBA. My solution up until now has been to...

## Range, Select, Change contents, Allignment or Offset?

excel,vba,excel-vba
I have a case at the moment where I am moving down the column with the names below and clicking on a macro, that then marks the indicator with a 35, a few columns down to the right. Due to the nature of the page, I am wanting to count...

## How do I use the Find function with a variable term, for example Run1, Run2, RunX

vba,excel-vba,loops,variables
Cells.Find(What:="Run:1", After:=Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) This is the code that I want to use, but I would like to loop it to find Run:1, Run:2, Run:3... Etc..., Is there a way to add a variable in a reference to find "Run:X"?...

## (VBA Excel) Extract Text and related Letter from String and output result

regex,string,excel-vba,character,number-formatting
So the scenario I have is there are letter with a number: Desired Output after Program (Note an underscore is visually used but I need a space: ____________F __G _____E __G__E______F Currently I have written code in the Number and First Letter Column to extract the number and first letter:...

## VBA to load very large file in one go (no buffering)

excel,vba,excel-vba
I am experiencing an unexpected vb limitation on the string max size, as explained in this post: VBA unexpected reach of string size limit While I was expecting to be able to load files up to 2GB (2^31 char) using open path for binary and get function, I get an...

## Excel VBA range after filtering xlCellTypeVisible

excel,vba,excel-vba,filter,range
What I want to accomplish: Open a workbook from a specific address, Filter the first column for value equal to 36 or 541 (I got this first part working), then check column 3 to see if a value of 2 exists and if it exists then filter out everything except...

## Simple Enquiry with Complex Answer - How do I Select RowA6-Row(last non-blank) for a simple formula

excel,excel-vba,cell,calculated-columns,calculated-field
I have many columns all labeled with many many values underneath, which can be words or numbers Here is the current equation =INDEX(AK6:AK94,MODE(MATCH(AK6:AK94,AK6:AK94,0))) I have this on the in cell 5 of each column. The number of values in each column may increase or decrease. If i reference the entire...

## VBA Application.OnKey Procedure error - Cannot run Macro

excel,vba,excel-vba
I am working on a module which uses an onkey event to detect if backspace or delete is pressed, if it meets the criteria for the event, the corresponding cells will clear contents. I have a procedure to launch the corresponding sub, but I get an error 'the macro cannot...

## VBA code to sum totals grouped by invoice

excel,excel-vba
I am new to vba and would like to automate a rather manual process in excel 2007. I have a list of invoice numbers in column A (can be up to about 500 lines long), a list of items and any associated accessories in column B and the invoice value/amounts...

## Concatenate N number of columns between 2 specific column name in VBA Excel

excel,vba,excel-vba
I am trying to concatenate the selected range between 2 specific columns. My first column name is "Product-name" (First column is fixed) and second specific column is not fixed. It can be 3rd, 4th, 5th or N. The name of that column is "Price". I want to concatenate all columns...

## Excel VBA - ShowAllData fail - Need to know if there is a filter

excel,vba,excel-vba,filter
I have automated a proper record input into the table that I use as a database, and when the table is filtered the input don't work. So I have code this to unfilter DataBase before every record input. Public Sub UnFilter_DB() Dim ActiveS As String, CurrScreenUpdate As Boolean CurrScreenUpdate =...

## VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

## Excel VBA (via JavaScript) - Moving a Sheet to a new location

javascript,excel,vba,excel-vba
With the following code, I am attempting to move a Sheet in my Excel workbook from one location to another. However, instead of making the move - Excel creates a new Workbook. How do I move a Sheet from one location to another within the same Workbook? /////////////////////////////////////////////////////////////////////////// // //...

## VBA to remove special characters before saving workbook

vba,excel-vba,special-characters
I found this function to remove special characters. Function Remove_Characters(s As String) As String Static RegEx As Object If RegEx Is Nothing Then Set RegEx = CreateObject("VBScript.RegExp") With RegEx .Global = True .Pattern = "\\|/|:|\*|""|\?|<|>\|| " End With End If Remove_Characters = RegEx.Replace(s, "") End Function What I want to...

## EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba
I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

## read variable excel data into variant with the same format

excel,vba,excel-vba,variables,data-structures

## VBA - Trapping events on dynamically created Textbox

excel,vba,excel-vba,events,userform
I am writing a VBA application in Excel. I have a Userform that dynamically builds itself based upon the data contained in one of the worksheets. All of the code that creates the various comboboxes, textboxes and labels is working. I created a class module to trap OnChange events for...

## Exit Sub And Call another Sub

vba,excel-vba,call
I have two subs, sub1 and sub2. I want to exit sub1 completely and start sub2 if a condition is satisfied. My attempt is (running from : sub1 . . If x=y Then Exit Sub And Call sub2 End If . . End Sub ...

## Match Function in Specific Column Excel VBA

excel,vba,excel-vba,match,worksheet-function
I'm trying to write a program in VBA for Excel 2011 that can search a column (which column that is is determined by another variable) for the number 1 so that it knows where to start an iteration. Say that the number of the column is given by colnumvar. The...

## Regular Expression for HTML

regex,excel-vba
From this string: <strong class="big zindex-pt zindex-pt-all">&pound;2,362,214</strong> I am trying to get &pound;2,362,214 using this RegEx in Excel VBA: .Pattern = "<strong class="big zindex-pt zindex-pt-all">([\s\S]*?)<" Any ideas what I am doing wrong?...

## Excel VBA 2013 Print Image

image,excel-vba,printing
I would like to print an image that shows in a frame on a userform. frame1.Picture = LoadPicture(spath & xPicture & ".jpg") spath is the full path to where the picture is stored. This shows the picture just fine. Now I would like to be able to print it. Is...

## VBA - do not grab header in range

excel,vba,excel-vba
I have code that looks for the header "CUTTING TOOL" using a .Find method. It loops through multiple files and multiple worksheets in the opening files. I have run into the problem that when it goes through multiple worksheets in one open file and the column is empty under the...

## Excel VBA code for multiple vlookup

excel,vba,excel-vba,match,vlookup
For a conduit network, I am trying to find the pipes that drain to a manhole. There can be multiple pipes that can drain to a single manhole. My data-structure are organized in the following way: - Stop Node Label ........ ................ - MH-37 CO-40 - MH-37 CO-40 - MH-39...

## Errors 91 and 424 when iterating over ranges in Excel VBA

excel,vba,excel-vba,range
I am an absolute VBA beginner. I have been trying to create a function that separates a large range into smaller ranges. However, when I try and iterate over the large range, I get errors 91 and 424 interchangeably. Here is the relevant bit of code: Dim cell As Range...

## Excel VB Listbox losing Value and Text Property

excel,excel-vba,properties,listbox
I'm trying to solve this problem along 2 days but I've not found the solution. I have a lot of listboxes on my excel and each of these listboxes are filled with different data, also I use these listboxes to change some filters at a pivot table using a VB...

## Excel Doc macro yields “Named argument not found (Error 448)”

excel,osx,vba,excel-vba,excel-vba-mac
This snippet of code appears to run perfectly fine in Excel on Windows. But on OSX, its erroring out with a "Named argument not found (Error 448)". With Sheets("Colors") Set rangeFound = .Cells.find(What:=Resource, After:=.Cells(1, 1), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) End With ...

## VBA Return Carriage and Fill Code

excel,vba,excel-vba,return,carriage-return
I'm really new to vba and would appreciate any assistance in the following problem I'm having. Problem description (in relation to diagram below): 1*) In c, I have managed to separate the return carriages, which leads to 2*) now that each return carriage has it's own row, I need column...