FAQ Database Discussion Community

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

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

## How do I get a cell's position within a range?

excel,vba,excel-vba
How would I go about getting the relative position of a cell within a range? Finding the position of a cell in a worksheet is trivial, using the Row- and Column-properties, but I am unsure of how to do the same within a range. I considered using the position of...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

## Open Connection and Recordset objects to use SQL for sheet to sheet data movement

I am trying to open Connection and Recordset to use SQL to move data between worksheets within the open workbook. To be clear all the data source sheets are open in the current workbook that I am trying to copy data from and to a different worksheet within the same...

## VBA macro - specifying columns with copy paste.

excel,vba,excel-vba
I have a partially working VBA macro which copies from one sheet 'AverageEarnings' to one of two ('SicknessRecordGraded', 'SicknessRecordUngraded') on condition that a string is found in column 41 ("GRADED", "UNGRADED"). I want the macro to copy values from columns B,C ('AverageEarnings') to columns A, B on the other sheets,...

## MACRO that organises, groups, outlines (Need to adjust the area that the macro applies to)

excel,vba,excel-vba
How to apply this from A4 instead of A2. Everything else I am happy with. I just want to understand any changes that I need to make to this. Is it needing changes at "set population"? The 2? Sub formatresults() Dim lastRow As Long Dim pop As Range Dim rpSet...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

## Excel vba code using ThisWorkbook.Sheets(1).Range not working, but Sheet1.Range works fine. Why?

excel,vba,excel-vba,object
When I use wb.Sheets(1).Range("A:A").Find(What:=ID, LookIn:=xlValues) I get error 91 - Object Variable or With Block not set. When I use Sheet1.Range("A:A").Find(What:=ID, LookIn:=xlValues) it returns correct value. Why the difference? Is there a flowchart I can reference or any simple information available to understand which sub-commands (I don't know the proper...

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

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

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

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