FAQ Database Discussion Community

## Excel Search VBA macro

excel,vba,excel-vba
I have been given the task of searching through a large volume of data. The data is presented identically across around 50 worksheets. I need a macro which searches through all these sheets for specific values then copies certain cells to a table created in a new workbook. The macro...

## Referencing a new inserted column Excel VBA

excel,vba,excel-vba
I am trying to reference a cell in the below formulaes. 'AUA Summary'!$D$9 . Each time the macro runs a new column D is inserted. The Problem: When the column is inserted my reference moves to ** 'AUA Summary'!$E$9. How do I get to reference 'AUA Summary'!$D$9 even if a...

## Doing a same action to all the subfolders in a folder

excel,excel-vba
Given below code converts all xlsx files inside "C:\Files\Bangalore" to csv files. Sub xlsxTOcsv() Dim sPathInp As String Dim sPathOut As String Dim sFile As String sPathInp = "C:\Files\Bangalore\" sPathOut = "C:\Files\Bangalore" Application.DisplayAlerts = False sFile = Dir(sPathInp & "*.xlsx") Do While Len(sFile) With Workbooks.Open(fileName:=sPathInp & sFile) .SaveAs fileName:=sPathOut &...

## Retrieve Number from a website into Excel

excel,excel-vba
From this website http://bit.ly/1Ib8IhP I am trying to get this number into an Excel cell. Avg. asking price in Bayswater Road: £1,828,502 Is there any way using VBA or another tool? Couldn't make it work with a web query....

## Excel - Open a named userform

excel-vba,userform
Sure this is very basic, I had userforms that I had not named and I called these by using UserForm1.Show. Now I have named these eg. ProgressBar. I call this one by UserForms.Add("ProgressBar").Show vbModeless But i cant figure out how to make this bit work: Private Sub UserForm_Initialize() 'With UserForms("ProgressBar")...

## VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba
I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

## VBA Debugger shows only 256 elements of a collection

excel-vba,debugging,collections
the title explains my problem. Im working on an excelmakro and my questions is, is there an option that the debugger shows all elements like it does with an array? And if not, is the only workaround the storage of my classobjects in an array after inflating the collection? I...

## 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...

## Taking value of a range of merged cells

excel,vba,excel-vba
I have a range of merged cells with values in them. I would simply like to read the value contained in each merged cell. The amount of cells in the merge area varies. Right now I have this: Sub testnoms() Dim cell As Range For Each cell In ActiveSheet.Range("B20:K23") 'this...

## How to make excel chart include all blank cells from range specified in VBA?

excel,vba,excel-vba,charts
In my code, I create a chart and specify a range for the input data: .SetSourceData Source:=Sheets("Output").Range("N6:CS6") The problem is that the first few cells are blank sometimes and when the chart is created it ignores them and starts from the first cell containing a value. This in turn results...

## Changing the active cell

excel,vba,excel-vba,excel-2007

## 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...

## search compare columns sheet 1 & sheet 2 and change a cell in sheet 1

excel,vba,excel-vba
I really need some help. Problem: I have a workbook with 2 worksheets. Both sheets has headers. Sheet1 is a list of account numbers in column A and the same for sheet 2 column A. Now, what I need to do is this: if I place a date in column...

## VBA dictionary data type

excel-vba,dictionary,key
Many sources, e.g. https://support.microsoft.com/en-us/kb/187234, claim the key of a dictionary can be of many data types beyond string. However, when I tried integers and dates as keys, it fails. Can someone explain? I tried the answer of @Tim Williams below, it works. What I had before was Private Sub trial()...

## Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

## Right function vba

vba,excel-vba
I want to take everything to the right of the symbol in the string I used Right(enclosedValue, InStr(enclosedValue, "*") - 0) where enclosed value is 29,6 *60, so I need it to return *60, but now it returns ,6 *60 Sorry if this question has been asked already, I have...

## 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...

## VBA Excel SendKeys Macro

excel,vba,excel-vba
I have some result data in sheet Extract that I need to activate cell by cell and press enter. It's alright for 10 cells but I have over 40 000. So i wrote some macro Sub Refresh() Dim lastrow As Long lastrow = ActiveWorkbook.Worksheets("Extract").Range("Q" & rows.Count).End(xlUp).Row For j = 2...

## copy only one column if criteria is met (Need to adjust my existing code)

excel,vba,excel-vba
The below code works great for copying an entire row, how do I make it so I only copy over the first column. I have tried altering range with no success? Condition is in J, the only column to copy should be 1st one. Dim cell As Range Dim lastRow...

## Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

excel,vba,excel-vba,user-defined-functions
I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

## Excel - How to show the last date different worksheets were changed

excel,excel-vba
This is my scenario: I have an excel Workbook with a number of different Worksheets. This Workbook is given to project managers within my organisation to complete. I have a Worksheet within this Workbook titled "Main Menu" which contains buttons that navigate the user to a number of different Worksheets...

## Count amount of differences and return column name with count

excel,vba,excel-vba
i am finding the differences between 2 worksheets, the code is as follows: For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then mycell.Interior.Color = vbYellow difference = difference + 1 End If If mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then matches = matches + 1 End If When the...

## Find column with unique values move to the first column and sort worksheets

excel,vba,excel-vba,sorting
I have 2 worksheets with the same headers in different orders. Headers are I.D, Name, Department, Sales, Start date, End Date and a few others. What I am aiming to do is search through the workbooks in which the headers may be in different orders, find the column which has...

## Using a cell's number to insert that many rows (with that row's data)

excel,excel-vba
I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

## 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...

## Way to capture double click to open file?

Is there a way to have an Excel macro to check what file I double clicked on to open. When I open that file, the Add-Ins installed load first, then the file I clicked on loads. How can I write code inside one of my Add-Ins to check what the...

excel,excel-vba
I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

## Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP

vba,excel-vba,powerpoint-vba
All I want to do is copy a range from Excel and then paste this range into PowerPoint. When my range is manually copied from Excel to the clipboard... If I right click on a blank slide when pasting into PowerPoint, it gives me the option to paste "using destination...

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 ...

## Formatting specific part of text string in VBA

excel,vba,excel-vba,outlook,format
I am in process of creating a macro that will save the current workbook, create a new outlook message and attach the file to the message. My macro does that but I can not format the text in the body of the email to my liking. Dim OutApp As Object...

## Excel VBA - Add row & make active

excel,excel-vba
Good evening Please see the attached image for an example of my data. The strings in column A are grouped together. The below code is a WIP to achieve the following... Find the last occurrence of each delivery location & add a new row after. In the newly created row,...

## 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...

## 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?...

## 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 Compile error What is the cause and solution?

excel-vba
Set myIE = New InternetExplorer myIE.Silent = True myIE.navigate sURL myIE.Visible = False Do While myIE.Busy Do Until myIE.ReadyState = READYSTATE_COMPLETE Loop Loop Application.Wait (Now() + TimeValue("00:00:02")) Set HTMLDoc = myIE.document HTMLDoc.getElementById("loginID").Value = ICUSER HTMLDoc.getElementById("password").Value = ICPASS For Each oHTML_Element In HTMLDoc.getElementsByTagName("input") If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit...

## 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...

## Excel VBA Option Buttons - how do you unselect them?

excel,vba,excel-vba
I am trying to unselect radio buttons and I can't figure out how. My latest attempt looks like this: Sheet1.Shapes("STCT" & strNumbers).ControlFormat.Value = xlOff Where the name of the radio button is STCT04 (or 05, or 06, or... you get the pattern) and the number part of the name is...

## VBA Import Data

excel,vba,excel-vba
I have a problem and I think it's complicated. It's regarding importing data/information from a another Excel file and the data/information needed is on 2 sheets. The code is working but not the way It should be it doesn't seem to import the data and down below it will explain...

## Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents
I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

## 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...

## Delete the Range of Columns in Excel VBA

excel,vba,excel-vba
I am trying to delete the range of columns but getting error "Application Defined or object defined error". In the below code wks is the worksheet and colStart and colEnd are variables with values. Dim colDelete As String colDelete = wks.Range(wks.Cells(1, colStart), wks.Cells(1, colEnd)).EntireColumn.Delete ...

## 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...

## 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...

vba,excel-vba
Having a little trouble with my userform, its not unloading once i hit the command button the data is inputted to the sheet but the userform is not refreshed and the data stays in the text boxes. It was working fine until i put the data validation in, but i...

## 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...

## Date formatting - making dd/m/yyyy into dd/mm/yyyy

excel-vba,date,format
I got a pretty simple question (but yet I've been stuck at it for some time now). Does anyone know how to make the date value from dd/m/yyyy into dd/mm/yyyy in a variable? dim lastdaylastmonth as date lastdaylastmonth = DateSerial(Year(Date), Month(Date), 0) So this code, as of now, would return...

## 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...

## 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...

## Check if excel file is open, if yes close file,if no convert csv file to excel Visual Basic [duplicate]

excel,vba,excel-vba
This question already has an answer here: Detect whether Excel workbook is already open (using VBA) [closed] 7 answers I'm having a problem creating a condition. Please see pseudo code below. thanks in advance Check if File A.xls is open If File A.xls is Open Close File A.xls Else...

## Vba changing directory to save

excel-vba
I'd like to save a file in a directory whose name changes according to the name of the Previous Month. Here is the code: name_month = MonthName(Month(Date) - 1) Set Newbook = Workbooks.Add With Newbook .Title = "TESO1" .SaveAs Filename:="C:\Users\ee31264\Desktop\Mensile Automat\name_month \send\TESO1.xlsx" End With Newbook.Close unfortunately the name_month i wrote...