Questions tagged [vba]

45838 questions
1

votes
0

answer
178

Views

Add Comments to Excel pivot slicer options

I have been trying out searching multiple forums before posting this question here. Issue: In one Excel pivot table slicer I have almost 50 options. I have given codes to each slicer option rather than writing entire description about the slicer option. I am looking for some VBA help where in I can...
excelislife
1

votes
0

answer
31

Views

How to fetch data in R by executing command in excel?

I have to fetch data from Reuters and they provide an excel plugin for that. The problem is that my excel is crashing if I try to fetch too many variables at the same time. I was wondering whether I can do it from R via some excel connection. In general I want to give the command to excel (from R),...
Nikhil Vidhani
1

votes
1

answer
1.1k

Views

How to reverse search in Excel?

I have a text in a worksheet like: The girl is very beautiful I want a formula to perform a search from right to left for the word 'very', and if found then extract it to some other region of the sheet. Note: Purpose of doing reverse search is because I want to implement it in my workbook which re...
Chen Guo
1

votes
1

answer
94

Views

Object variable not set (Error 91) when using AHK to close an Access Application

I have an Access Form named 'Form1', and in the form it has a couple of subs like, Form_Load, Form_Unload, etc. I want to use an AutoHotKey script to control the opening and closing of the form. Form opening code: acc:= ComObjCreate('Access.Application') acc.OpenCurrentDatabase('\\...\target_databa...
Aaron_Geng
1

votes
1

answer
45

Views

How to skip specific columns in a Row copy

So, I'm looking for a way to skip specific columns in a row copy. I'm working with doing a bunch of loops to copy/paste from a bunch of sheets of variable sized reports and I want a way to simply skip a column or columns in a row copy since I can't just do a entirecolumn.delete to deal with the exce...
George
1

votes
2

answer
53

Views

Excel VBA to Remove Opt-Outs

I am a military recruiter and am trying to use autofilter to filter out a range from another range. I got this from another stackoverflow page but can not figure out how to change that string strSearch to a range like [email protected], [email protected], [email protected], etc. We get lists of leads but I'd like to keep t...
Joe
1

votes
0

answer
94

Views

Creating a save as and send command button in word

I am trying to minimize a daily task by creating a form that will have a save as/send command button. After searching similar posts, I found the code below: How do I define the 'TagNum', 'Date' and 'NTID'? strTagNum = ActiveDocument.SelectContentControlsByTitle('TagNum')(1).Range.Text strNTID = Acti...
azessman
1

votes
0

answer
38

Views

I am using a cell value in Excel to look up an array based on that cell value

I am using =VLOOKUP(C$2;INDIRECT(C$2);6;FALSE) One of the inputs (a serial number) is MK05391. When I create the range for this row and name it the same as the serial number it won't let me. Says that The name either does not start with letter/underscore, or there's something else namned the same th...
Vrig
1

votes
1

answer
74

Views

How to get dynamic node and element values in xml using vb?

I am very new to vb. In my XML file all the attributes, nodes and elements are dynamic(Healthcare.xml). I am trying to get the values in the below manner. page: login, method: login, args: ad59090,GA124444 In some cases, there will be multiple methods and arguments. page: benefits, method: tagbenef...
Nandan A
1

votes
1

answer
63

Views

Unable to Click Submit button in webpage using .net

Im creating a automation using VB in microsoft Visual Studio like a app the project path is Visual Basic>windows classic Desktop>windows form app(.NET framework). Code is written using VBA and it is a form which gets user input and paste it into a webform and click a submit button and create a T...
Ashok Kumar
1

votes
0

answer
326

Views

Trouble with sending a left click

I have a series of clicks and cursor movements I have in a macro. Everything works fine until the end. All the clicks work until the last click. I have tried to put various amounts of sleeps in between the clicks and before the clicks. Whats odd is that when i click for real it works! ahh Whats also...
Justin Captain Wypij
1

votes
1

answer
210

Views

VBA Run-Time Error 1004 on ActiveCell.FormulaR1C1

The formula in Excel cells '=DATEVALUE(IF(ISERROR(INT(LEFT(MID(D2,FIND('/',D2)+1,LEN(D2)),FIND('/',MID(D2,FIND('/',D2)+1,LEN(D2)))-1))),DAY(D2),INT(LEFT(MID(D2,FIND('/',D2)+1,LEN(D2)),FIND('/',MID(D2,FIND('/',D2)+1,LEN(D2)))-1)))&'/'&IF(MID(D2,LEN(D2)-4,1)='/',INT(LEFT(D2,FIND('/',D2)-1)),MONTH(D2))...
Eric
1

votes
3

answer
541

Views

Excel VBA ByVal Target As Range Does not update Target Worksheet when excuting from a function cell

I'm having a issues with copying a value from a cell that is using a function to get its value from another cell. Insert value into A1, A3=A1 and when ever the value of A3 changes the changes need to transfer automatically to column H. The issues i having is that if i type the value into A1 then A3...
Kenneth O'Donnell
0

votes
1

answer
14

Views

How to read multiple text files (with multiple lines of data) into a two dimensional array?

DESCRIPTION: I have 10 text files. And I want to read them into a two dimensional array. Each file looks like this and the number of lines vary. There are 5 lines in this text file but other text files may have more or less than 5 lines. No. Location(ft) Mix Near Far Comp. Height(ft) B(in) D(in) An...
lhp2290
1

votes
0

answer
35

Views

How to access the same session used by WinHttpRequest in a new browser instance?

I would like to automate a currently manual process in retrieving multiple cookie data (e.g. both 'sessionToken', and 'sessionID' from cookie data that's been sent out in a GET request header, which is triggered from browsing to the URL page) as well as parsing a value from the page's inner HTML (e....
intangibleAssets
1

votes
2

answer
278

Views

Excel VBA Changing Combobox list from static range to dynamic

This should be pretty simple, but I am struggling. Right now, this code works: cboCategoryEdit1.List = Sheets(2).Range('A2:A40').Value I am trying to 'clean up' my project by changing how the combobox is populated. I'd like it to be a combobox with a range that only takes populated cells. Meaning...
jpend314
1

votes
1

answer
221

Views

Access VBA: Invalid field data type

With an Access VBA, I am trying to load a file from a directory to the table [table name] in the field [field name] which Data Type is Attachment. The problem is that I get the error Invalid field data type. The code is the following. Dim SQL As String SQL = ' SELECT * FROM [table name] WHERE ID = '...
1

votes
1

answer
37

Views

Folder Explorer is Not Work well in Excel VBA

Private Sub ButtonPath_Click() Cells(1, 32).Value = FolderExplorer() End Sub Above is my UserForm's Code. Below Code is My Module's FolderExplorer() Function. '//Source : http://software-solutions-online.com/vba-folder-dialog/ , edited by me. Public Function FolderExplorer() Dim intResult As Integer...
박영종
1

votes
2

answer
1.8k

Views

Run Macro without opening Excel

This is my first time using VBs. I used to it to automate Excel macro but I failed due to the spacing between filenames. It works fine if the space was removed. I read this post and used the double quote method but did not work. This is how I used it objExcel.Application.Run '''C:\Users\account\Desk...
Max
1

votes
0

answer
112

Views

Batch Print email attachments in order by date

Issue: Hello, I have a macro, which saves me for a lot of clicking, but in the end not so much time, when it all comes down to it. The code allows me to mark everything within any mail folder. It will print the attachments within the email. After this the error starts. Whenever I press this button,...
Patrick S
1

votes
0

answer
414

Views

Highlight Cell in one Column BASED on Conditional Formatting of Other Columns

I am a beginner with Macros. I slightly changed my code below to include conditional formatting based on an Input Value. The code below applies a conditional format (orange highlight) to 6 specific columns based on whether those columns contain a specific text. I would like to add another step afte...
NoeyPI
1

votes
2

answer
238

Views

How to add dynamic comment to column with validation list?

Given an excel column with a validation list (dropdown). How do I show a dynamic comment based on the selected option? Thanks
fabian
1

votes
0

answer
93

Views

Copy Range of cells to Visio (2013) using VBA code

I have an Excel range of cells (let's call it 'Table A') and I would like to copy that Range/Table into Visio using VBA. Without VBA - I can copy the range in Excel, go to Visio - Insert - Chart - go to Sheet1 tab - Paste Using VBA - I am a novice so this is what I think it should look something li...
Shadowcast
1

votes
0

answer
54

Views

Combining vba filters

Does anyone know how to combine these two lines so that when I run the code rows that contain Oranges do not appear. At the moment, only the first filter is working. So I get all of the Apples, but the second filter does not. Thanks in advance! ActiveSheet.Range('B4', Range('BT4').End(xlDown)).Auto...
Gandalfrandalf
1

votes
0

answer
44

Views

How can I compare and change cells in Excel

I have two sheets. In both sheets I have a column (B) where each row is a different name: Sheet 'A' has names in the column, these names are 'leading'. Sheet 'B' also has names in this column, these names CAN BE outdated. What I want: A macro which can check the names. delete the names in Sheet B...
Adventador
1

votes
1

answer
52

Views

Save Specific Attachment File Types

I have VBA code to automatically download (save) received mail attached files. I need to make a condition to only download (save) .xlsx or .jpg files. Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem) Dim oAttachment As Outlook.Attachment Dim sSaveFolder As String sSaveFolder = 'C:\Users\D...
user8205502
1

votes
0

answer
531

Views

Excel slicer multi-select functionality turns off when workbook is closed?

I have a workbook that contains a report with tables and slicers. Currently, when the user opens the workbook, they must manually click the 'multi-select' option on the slicer. Is there a way to keep this permanently selected (even when the workbook is closed and re-opened)? Thanks!
Arjun Arun
1

votes
0

answer
285

Views

Excel VBA - Pictures do not display when I add Private Sub macro

I have images pasted (not linked) into my excel workbook. When I try to add any Private Sub macro code, the next time I open my workbook, the images come up with the message 'This image can not be displayed'. However, images on other sheets display correctly, and if I save the workbook on a differen...
mcflame
1

votes
0

answer
293

Views

Excel VBA Find Date - Run Time Error

I have a problem with my VBA code and I hope someone can help me. What I'm trying to do here is the following: I have a worksheet (Sheet 1) with data that gets updated every day. In order to preserve this data, I want to copy and store the source data on another worksheet (Sheet 2). Sheet 2 has colu...
Willemien
1

votes
0

answer
58

Views

Searching and sorting ranges in excel with toggle buttons

I have a spreadsheet containing ±100 named ranges (5 rows, 15 colums) which are sorted based on time. My plan was to make toggle buttons to hide or unhide named ranges when they contain a certain value. So when I select the toggle button for 'Opel' for instance, all the named ranges containing 'Ope...
Tommy
1

votes
0

answer
379

Views

Enabling Run-a-Script option in Rules Wizard (Windows 7, Outlook 2016)

The 'Run a script' option is disabled for security by a Windows update. Here https://www.slipstick.com/outlook/rules/outlook-2016-run-a-script-rules/ you can see the difference before and after the update: 'start a application' is missing. If you want to look at a screenshot from me (after Microsoft...
Sascha
1

votes
2

answer
1.9k

Views

Run SQL Update Query in MS Access VBA

I am trying to run this code to fire an update query from VBA. Access is giving me a syntax error. I suspect this has to do with the fact that I'm trying to run an update query using an INNER JOIN with a form. Is what I'm trying to do at all possible? Private Sub Btn_Edit_Data_Click() Dim db As DAO...
OneLineAtTheTime
1

votes
0

answer
136

Views

EXCEL VBA: Testing for non-numeric input data

I am developing a macro program in EXCEL 2016 VBA. I am currently experiencing a problem with my code for one of the initial pre-processing steps: I am trying to check for non-numeric data selected by the user. I have the following example data: My code takes the selection specified by the user and...
Dan
1

votes
0

answer
148

Views

Error Message Occurs in Saving Excel Files when I use Excel VBA Function

When I try to save the below code it gives an error message and I can't save it. below are my error message's image file link. I'm using Korean Excel 2007, So I don't know what exactly this message is in English but I can give you the meaning of this error message. (this error message means... could...
박영종
1

votes
0

answer
30

Views

Conditional colorscale - programmatically

I am struggling to recreate logic behind Excel's three-colors scale for conditional formatting. This is the second option displayed when you choose Home-->Conditional Formatting -->Color Scale (red-yellow-green). I have started with this subroutine: Sub Tricolore(Rng As Range) Dim cl As Range Dim d...
MarcinSzaleniec
1

votes
0

answer
62

Views

Embed excel into word using vba does not always display the top of the embedded sheet

I have a Word document with VBA code to embed multiple excel sheets using the following process i.e. addOLEObject Set IllSheet = _ ActiveDocument.Bookmarks('example1').Range.InlineShapes.AddOLEObject(ClassType:='Excel.Sheet.12', filename:=filename1, LinkToFile:=False) however on occasion the embedde...
M.Hung
1

votes
0

answer
28

Views

Maintain source formatting with column reorder Excel VBA

I have been importing data from a downloaded report into a working-copy excel workbook for some time now, but someone higher up than me recently decided to change the report that I download for my information. The easiest solution seems to be to re-order the information in the report workbook befor...
Jon
1

votes
1

answer
246

Views

Excel VBA loop through cells to find Conditional Formatted cells and copy paste on other sheet

My issue: I want to loop through a range, and whenever it finds a conditional formatted cell, it should copy the cell to the left to the cell to the right of it. And then afterwards paste it into an other worksheet. My code loops through the cells easily, but doesn't copy the information, and paste...
Patrick S
1

votes
1

answer
30

Views

Unable to Iterate Columns of a Selection — error '424' Object Required [closed]

I'd like to be able to loop through the columns of a selection and do arbitrary things such as set column widths through some calculation. However, all of the variations I've tried that run-time error '424', Object Required. I've dug through the documentation, these forums, and Google, yet the cl...
Nil Rift
1

votes
0

answer
149

Views

How to extend or merge interfaces in VBA

I am creating a custom collection class in VBA, whose members must implement two interfaces. One is a library interface IComparable which allows me to sort my collection, the other is a home-made interface ImyObject which guarantees certain properties of the members of the class. I ensure only the c...
Greedo

View additional questions