Newest vba Questions

Q&A for professional and enthusiast programmers

Search and substitute a value when comparing information in two tables [on hold]

I am currently hit a roadblock with a macro that I am trying to create: I currently have a master table (I actually have several master tables depending on the region, but you get the idea) that ...

Tying multiple conditions IsEmpty Cell.Value and Offset

I would like to highlight cells that meet a certain set of criteria's. All cells between columns D through Q are the range that should be effected. The cell value must equal "y". The adjacent cell ...

VBA excel how to show output in user form [on hold]

I have created a userform which will ask for the Day of the week and month, year and name of the month. The user form resembles something like this: Entry Date -Enter the Month Name: -...

Adding external links from SharePoint to Excel Workbook

I need an advice. I have major data in excel sheets on SharePoint, and I need this data in my workbook on desktop so I can automate my master sheet in that workbook using index/match/v-lookups etc. So ...

Trying to copy and paste using different workbooks, but the correct value is not being copied [duplicate]

I am trying to copy and paste in two different workbooks, but the correct value is not being copied. I believe it is because I am using the "with" statement within another "with" statement, but I do ...

How To Reference Chart Objects within a Table

I have a report in Word with text and images/charts organized into tables. I'm trying to set it so that a user can hide/show these tables by pressing a ToggleButton control on top of each table. I'...

Type Mismatch error when trying to insert index match formula using vba?

I am trying to insert the following index match formula with wildcards using vba like so: Cells(i, 13) = "=IFERROR(INDEX(Contacts!$C:$C,MATCH(" * " & Range(""C"" & i).Value & " * ",...

vba insert range into a specific column

Dim CopyRng As Range LastR = LastRow(Sheets("Sheet3")) startrow = 1 startrow = startrow + 1 Set CopyRng = Sheets("Sheet3").Range(Sheets("Sheet3").Rows(1), Sheets("Sheet3").Rows(LastR)) 'insert ...

Copy and paste is too slow for copying notes

I wanted a macro that could copy powerpoint notes to a text file and I found this macro online. However I found that obviously didn't preserve formatting which I required so I wrote this macro instead....

IF and AND statement VBA macros not performing properly

In my code I have the following IF and AND statement to which it is not working properly. Private Sub CommandButton1_Click() Unload Me Dim iRow As Range Dim i As Long Dim ws As Worksheet Set ws =...

VBA conditional formatting cells with value that are greater than or less than

I'm trying to write a macro code that will do the followings for a range of cells Conditional formatting will only apply if the cell has been entered a value If the value in the cell is less than $I$...

Application.inputbox missing prompt

I've learned that Excel VBA on OSX has been hobbled, but is this a problem in my code or VBA for OSX? The following line: FirstYear = Application.InputBox(prompt:="Enter the first year you wish to ...

Excel Formula in vba?

I am trying to insert the following formula using vba: Cells(i, 17).Formula = "=IF(""" & Range("M" & i).value & """ = """","""",IFERROR(INDEX(Contacts!$D:$D,MATCH(""*"" & """ & ...

Excel VBA value dynamicly named textboxes

Bottom Line Fix: Changing my dynamicly named textbox to have a _ separator between the column and row to get rid of the ambiguity of the names. Previous Code: Set cCntrl = PickTicketForm.Controls....

Access 2010 w/SQL backend - send multiple reports to pdf using vba without preview

Scenario is an application using Access 2010 front end, SQL backend. Need exists for user to select one or more reports from form, then click button that runs through choices and runs report using ...

Save Error - VBA Debug Error 424 [on hold]

Save error on Line "With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow" Error 424 Sub customerSave() Dim set1, set2 Set set1 = Worksheets("CustomerList") Set set2 = Worksheets("...

Adding a “Home” hyperlink to a shape using VBA

this question has been asked before but the solution I found does not seem to work, no matter what I try - Here's my problem: I have several worksheets in a workbook. I created a macro that creates a ...

Excel VBA: Disable Auto Open when Save As function is run

I am running a code that takes the data that is input and creates a copy of the file. That is fine; however, the new file opens every time it is saved. I would like to save the file without it opening ...

New fields in a table, fill via recordset

I added a field to a table that already contains data, called "FieldNew" in this example. Now I try to fill that field with data from a form, in a VBA script. Set rs = .OpenRecordset(...) rs.Edit rs....

VBA to maintain format during copy paste

I have a Macros enabled workbook(protected sheets). Users are allowed to provide the inputs to the cells that are unlocked. The problem here is when users copy paste the data from other applications, ...

Me.undo in VBA Access 2013

I have an issue with the ME.UNDO command to prevent changes when closing a form in Access 2013. I have a form that links to a table which contains a worklist. This form is read-only to prevent ...

Can I Return From The Current Event Stack In Microsoft Access VBA?

I would like to know if there exists a pre-built system function that would cancel the whole memory stack of the current event being fired, without having to exit every procedure in the stack trace, ...

I am getting an error “ The object invoked has disconnected from its clients” when automating my website

Below is the code i am using Sub IE_try() Application.ScreenUpdating = False Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True IE.Navigate "my site" Application.StatusBar = "...

How to find the first cell of a column and write in it? Excel VBA

I need to get the code that can find the first cell of a column and write in it. I have something written from the cell A1 to A5. I have a button and each time I press it, I want it to write the same ...

Use ADO recordset and connection to update same #TEMP table

In VBA, I am trying to use an ADO recordset and a connection to perform separate operations on a SQL Server #TEMP table. I would think that in the code below the recordset and the connection are ...

selecting 30 cells from the bottom active text to up excel vba

Hello i would like to copy the last 30 cells of my column B. the below code is copying to right, but i want it to copy only the 30 last cells of the column b Sub test() Range(ActiveCell, ActiveCell....

macro newbie pasting formula to adjacent cells [on hold]

I'm a newbie when it comes to macro. i just want to create a macro that will auto copy the formula (not the format) on a specific row but will depend on the number of rows on the adjacent column. ...

How to replicate the function of INDIRECT(ADDRESS) in VBA

I need a function that adds one to the value of a cell whose position is determined by an RNG. The function or macro needs to be triggered by a button. I have a function that runs dynamically to draw ...

VBA dijit editor input

I'm fairly new to integrating VBA with IE. Right now, I have a form that logs the user into an internal site and inputs some information from a form that they fill out. Currently, I'm using the ...

add a formula when a row is added vba

I want to add the following formula in columns L to R when a row is added. So far this is what I have. But I can't seem to figure out how to ensure that this macro ONLY occurs when a row is added ...

I am trying to write a script that will extract hourly values from a web page table into excel

The table is in 15 minute intervals but I only need the values that are every Hour. Is there a way to tell the vba to only read the values that are timestamped to HH:00:00? Sub GetWaterLevels() ...

Simplify my code for ~350,000 row look up.

I have a list of ~350,000 lines of data that I need to sort through and paste results onto a new WS. The first 12 columns are weights and the second 12 columns are qualitative values. I need to search ...

Optimizing vba looping code to run faster

Sub Testingloop() Dim endrown As String Dim ex As String Dim ez As String Dim eh As String Dim eg As String Dim el As String Dim ee As String Dim es As String Dim ef As String Dim ei As String Dim i ...

multiple conditions in a vba do while loop

I have a report that can be any where from 5 to hundreds of pages that provides patient information based upon lab tests. Each patient record is separated by underscores with a check box at the end. "...

MS Excel VBA - Copying SpecialCells while keeping the destination formatting

I would like to copy these special cells to a destination, while keeping the destination's formatting. I'm thinking I have to put .values somewhere in the lines. The code is as follows: ...

Getting -1 from .recordcount on MSSQLS linked table Access VBA

Title says it all - my .recordcount on MSSQLS linked tables are returning "-1". How do I account for this? my code: Set db = CurrentDb() For Each td In db.TableDefs If Not (td.Name Like "MSys*" ...

Excel VBA Array For Each Unique Values

i try to create an array and fill it with unique values. Due to check if the value is unique, i fail to create a basic "for each" procedure. I just want to create a unique random number, put it into ...

How to download the most updated file from Sharepoint with VBA

I am having trouble downloading the newest file from a Sharepoint site. I have a Sharepoint folder with only one file in it. This file will be over written with the same name each time there is an ...

Export an Array of specific slides in powerpoint to a PDF

I made a virtual lab in powerpoint for highschool students, once in a while there is a slide where they are asked a question. They can leave their answer in a Textbox. At the end i want to insert a ...

Loop through files in a folder and countif [on hold]

I need to use VBA to count all values that are not zero in all excel files saved in a folder and save the result in the worbook containing the macro

Vba select entire colum only used range

Does anyone know how to select entire column but only used cells. Normally the data is continuous. One issue is that sometimes the sheet is filled only for two rows sometimes for hundreds. What I ...

For Cell in Column Loop - Specify value for adjacent column cell based on location in loop

I've put together an excel VBA macro that loops through all used cells in column I, and checks if the value of the cell matches a word. If the word is a match, I would like to set a random number, ...

Refer to multiple selected Items in a listbox in ms-access

How do I select multiple items in the list box, then refer to the Items I have selected?

vba - If column contains more than 1 comma, delete [on hold]

Is it possible to do the followings on Excel using vba? If either of column C & F contains more than 1 commas, delete it If not, leave as it is. using batch file, if the excel file is in ...

Return how many words are into a cell

Could anyone help me with an advice regarding how to return with VBA the number of words from a cell? In Excel I use C2=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1, but it seems in VBA the "SUBSTITUTE" ...

VBA Word: Difference between Font.TextColor and Font.ColorIndex?

I have a macro that insert text. It was working well so far but... Now for some documents, I get an error 445 when it applies color. Here is the code: 'Some code before ActiveWindow.ActivePane.View....

How to make a macro formula breakout data from 1 sheet in a certain way depending on starting value of the row

Let's say I have fixed width demographic data text files for a company's employees. Each row is a different person, but there are two types of rows: 1 is the employee and 1 is a spouse/child. The 2 ...

copy a column in a filtered range to another column, partially correct, partially irrational

I need to copy column 19 to column 36 in a filtered range, however the results are only partially correct. As for the other part, I have no idea why it turns out that way. I clear out column 36 first,...

Convert VBA String to Double

I need help please! I am using the very basic coding of VBA for word to create a template that pulls data from other screens in windows. When it pulls the numbers, they are formatted as strings. I now ...

Adding cells to AddItem based on criteria

Is it possible to add the values of say column B to ComboBox2 if their corresponding cells in a different column match the value of ComboBox1? If I were to select a customer ID in ComboBox1, I want ...
Translating... 0%