FAQ Database Discussion Community

## Retaining original table formatting after a 'pagebreak'

excel,excel-vba,table,formatting,page-break
So here's the finished product, a statement of accounts with a working statement table, and an ageing analysis: Everything works great. It basically populates itself row by row with data from another table. Here is the sample code: j = 21 'First row on the statement of accounts workbook For...

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

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

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

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

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

## How do I store a SQL statement into a variable

sql,excel,excel-vba
I am currently facing a problem here. I have a column called "DESC1" in a table called "Master". I'm trying to retrieve the value based on something along the lines of this... "Select DESC1 FROM Master WHERE '" & TextBox1.Text & "' " And I'm trying to display on the...

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

## Using a stored integer as a cell reference

excel,excel-vba,reference
Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

## ReplaceLine method in VBE only replacing part of line

excel-vba,vbe
In response to this question I thought it would be fun to write a VBE macro that would automatically replace lines which look like DimAll a, b, c, d As Integer by Dim a As Integer, b As Integer, c As Integer, d As Integer In my first draft I...

## Object Variable or With Block Variable Not set in loop using find function

vba,excel-vba,loops,object,find
Sub Main() Dim FName As Variant, R As Long, DirLoc As String, i As Integer R = 1 i = 1 DirLoc = ThisWorkbook.Path & "\" 'location of files FName = Dir(DirLoc & "*.csv") Do While FName <> "" ImportCsvFile DirLoc & FName, ActiveSheet.Cells(R, 1) R = ActiveSheet.UsedRange.Rows.Count + 1...

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

## using activecell and offset in VBA

excel,vba,excel-vba
I am trying to test offset property in excel vba . I am using a simple code which selects the cell which is daigonal , i.e 1 row and 1 column away from the active cell. This is what I am doing Sub Range_Offset() Range(ActiveCell).Offset(1, 1).Select End Sub I have...

## adding variables into another variable vba

excel,vba,excel-vba
Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

## Slow VBA macro writing in cells

excel,vba,excel-vba,ms-project
I have a VBA macro, that writes in data into a cleared out worksheet, but it's really slow! I'm instantiating Excel from a Project Professional. Set xlApp = New Excel.Application xlApp.ScreenUpdating = False Dim NewBook As Excel.WorkBook Dim ws As Excel.Worksheet Set NewBook = xlApp.Workbooks.Add() With NewBook .Title = "SomeData"...

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

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

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

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

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

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

## INDEX MATCH VBA (return a value based on two criteria)

excel,vba,excel-vba,lookup,formulas
I'm doing some work for a client where they receive a weekly report of customers and regions (two columns, A for Customer & B for Region, in a worksheet). * I've omitted the other columns in this spreadsheet for the sake of clarity They need to match the price on...

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

## How to duplicate values from Cell to Cell in Excel?

excel,vba,excel-vba
I need to duplicate the n values from the column A to Column B Example: ...

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

## Compare 2 sheets with different headers

excel,vba,excel-vba,compare
I have 2 different files which have different headers, for example: OldfileHeaders | NewFileheaders ID | Test ID Date | New date and so on. I am trying to compare the data in both sheets and see if they match. The rows of data may be in different order and...

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

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

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

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

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

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

## Paste pivot table as bitmap into Body of Outlook Appointment

excel-vba,bitmap,outlook,paste,appointment
Using Excel VBA, I'd like to make a small pivot table visible to Outlook users. I do NOT want to paste into a message body I DO want to paste into an appointmentItem Here is my code that creates the Appointment and copies Range to clipboard. How do I paste...

## VBA - printing empty cells

excel,vba,excel-vba
I have code that takes information from under two specific column headers in opening files and prints them to a masterfile. One column is empty every few files and I need it to print empty cells to column 2 of my masterfile in the range of the filled cells of...

## How to insert data by using VBA in excel

excel-vba
I have 3 sheets (sheet1,sheet2,sheet3) in sheet1 has all user ID, sheet2 has logon user ID and sheet3 is empty. The point is... I need to put not logon user ID into sheet3 but my code fail. If it's a stupid question that because I'm newbie with VBA Here my...

## How to use each value in a range to autofilter data in another sheet?

excel,vba,excel-vba
I'm a bit new to VBA, so was hoping to get some help on this page from you experts! I have the following requirement: I have a table with unique values of Column2 in Sheet2(range) I need to filter out data in Sheet1 using each value in the above range...

## VBA for duplicate rows

excel-vba,duplicates
I have a sheet of columns. I want to compare data in multiple columns, and return a flag in another column to indicate rows that are duplicates. I found a little code online which was meant for checking one column of data, and have so far been unsuccessful in being...

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

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

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

## Copying certain columns VBA

excel,vba,excel-vba
I have a working VBA macro which copies from one spreadsheet 'AverageEarnings' to another 'Sheet1', under the condition that Column AO has the word 'UNGRADED' in it. The macro copies the entirety of these conditional rows to Sheet1. I am looking to copy columns B and C ('AverageEarnings') to columns...

## Macro or other solution in excel to automate interaction of data in two worksheets to come up with a third worksheet

I have no idea where to start with this problem (may be due to poor knowledge of terminology). I have two worksheets which I will put up in Google Sheets which I'd like to generate a third worksheet for (also in the same Google Sheets file). https://docs.google.com/spreadsheets/d/1ALQlQhNugUnQzM5NdbFsLX_TlhV6BzT_1TDQKc6hD5I/edit?usp=sharing I'd like to...

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

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

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

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

## Range.offset propety

excel-vba
I'm trying to understand a code on VBA, to debug the VBA program of someone. So I want to know that is the property and the meaning of Range (Value).offset(x;y). Here is my code: `Sub initialise() Dim i As Double, j As Double For i = 0 To maxnodes -...

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

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

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

## pick and transfer a column in excel without VBA

excel,excel-vba
I have the following problem. I have three columns in Excel, each with its own title/header. I want to pick and transfer one of the columns based on its title/header, without using VBA. Let me give an example. My data set is the following: A B C D E F...

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

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

## Msgbox With If statement

vba,excel-vba,excel-2003
I'm trying to get a box to pop up listing a number of values that could be blank. MsgBox (res1 & vbNewLine & _ res2 & vbNewLine & _ res3 & vbNewLine & _ res4 & vbNewLine & _ res5 & vbNewLine & _ res6 & vbNewLine & _ res7...

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

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

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