Questions tagged [excel-vba]

26493 questions
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
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
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
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
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
1

votes
2

answer
58

Views

I'm trying to extract data in the tag <a data-params> from Amazon.com website

I have set of Amazon URLS (https://www.amazon.com/dp/B01LTIORC8) in column A in excel and Im trying to extract 'B00M4L4MFC' data from ' href='/dp/B00M4L4MFC/ref=dp_cerb_1'' in column B using the below html tag. I found the below code online : Sub GetAboutUsLinks() Dim internet As Object Dim html As...
Sri Ram
1

votes
0

answer
73

Views

vba countif for duplicates when validating another data

I'm trying to copy data into new workbook and validate the data by removing duplicates keeping one cell value as the source. All I wanted is the count of XD in the worksheet provided there are no duplicate Record locator. Also I wanted the count to be in a msgbox. Can someone help ? Sub openworkbook...
Kathir1k
1

votes
0

answer
73

Views

DblClick Cell text cursor blinking

I have cell with value of string type. I programmed a Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) procedure to show UserForm after doubleclick on some cell - let it is 'B3' cell. The UserForms is showed after doubleclick but there is blinking text cursor somewher...
Juraj
1

votes
1

answer
822

Views

Get the address of the changed cell

I'm trying to trigger a macro (dependant on cell address) whenever any dde linked cell value is changed. I tried Workbook.SetLinkOnData and Worksheet_Calculate event (with a dummy formula to trigger the event), but the problem is that none of them returns the address of the updated cell. So is there...
user3286479
1

votes
0

answer
68

Views

How to skip particular text cell in Excel VBA

When I run below code how can I skip particular text cell from merging. Original data S.No. Clock No. 01 02 03 04 05 06 1 099566 W P p P P P 2 282420 P P P W P P 3 623738 P P W L P P 4 406290 P P...
1

votes
0

answer
493

Views

Run same macro on different worksheets

I have a workbook with 10 worksheets. I recorded a macro for creating a pivot tables from the first worksheet. When I run the macro in another worksheet it just created the same pivot table containing data from the first worksheet even I had replaced the worksheet name with ActiveSheet in the scrip...
Arayau
1

votes
0

answer
243

Views

Variant type mismatch at run time

I am trying to automate Bentley Microstation from Excel using runtime objects without project References for a proof of concept. Sub MSFromXlRuntime() Dim MS As Object Dim DesignFile As Object Dim Line As Object Dim startPoint As Variant Dim endPoint As Variant Set MS = CreateObject('MicroStationDGN...
Alex
1

votes
0

answer
39

Views

Opening a tiff file from Excel causing loss of Cell Edit functionality

I have a spreadsheet, with different people using copies on different PC's. There is a macro triggered by Worksheet_Selection_Change that opens an image file. On two of the PC's, when the workbook is opened, the user can't edit a cell without double clicking in it. Also, the Selection_Change even...
1

votes
1

answer
87

Views

VBA email reply - text does not add

I'm writting macro that finds an e-mail and replies to it. The problem is that the text I want to reply with does not add. Could you please tell me what I'm doing wrong? Sub Test() Dim olApp As Object Dim olNs As Object Dim Fldr As Object Dim i As Long Set olApp = CreateObject('Outlook.Application')...
Tomek
1

votes
0

answer
385

Views

Excel VBA ADO Recordset - Issue with SELECT columns

I get Runtime error '-2147467259 (80004005)': Unspecified Error when I use the following code: Set conn1 = CreateObject('ADODB.Connection') conn1.Open strcon1 strquery3 = 'SELECT * FROM TABLE_NAME' Set rs3 = CreateObject('ADODB.Recordset') Set rs3 = conn1.Execute(strquery3) However the code works w...
Rajat Solanki
1

votes
0

answer
83

Views

Close Internet Explorer msg box. FindWindowEX function? childHWND?

I'm looking for the solution to close IE browser message box, using Internet Automation in VBA. See attached photo of the screen. I am not considering keypress event as a good way to do it. However I found piece of the code which 'smells good' for a solution of this problem. https://social.techne...
Jacek Rzadca
1

votes
0

answer
37

Views

VBA Excel Script Save as TXT CSV, UFT-8, set specific field and txt delimeter and Quote all TXT cells

I have been trying for a few days to crack this. We have a Lotus Notes DB that will only accept and import of a file only if it is saved in this method 1) Opening a XLS in Open Office 2) Selecting all cells (always a single worksheet) and setting Default Formatting 3) Saving as TXT CSV 4) Then as p...
Kal-el
1

votes
1

answer
170

Views

VBA Runtime error 91 when already used SET?

I'm getting Runtime Error '91': Object variable or with block variable not set when I already used the set keyword. I read this old post that says set is used to assign a reference to an object. What does it really meaning? Also read about POST A & POST B but no much help. Dim strPath2 As String Di...
Max
1

votes
1

answer
575

Views

Excel Conditional Formatting and Rounding Issues

I am trying to find a way to use excel formatting to show the color red when a specific cell is not equal to another cell. However, there are times when the value is off by two or three numbers due to how excel rounds(ex: 593=594, 593=592).Is there a way to use excel formatting or any other way to m...
mikerosd
1

votes
1

answer
138

Views

Merge Data From multiple tabs to one master tab

I try to merge the data from multiple tabs to one master tab. Since the concepts in those tabs constantly updated every day (always adding the new information), I want to make sure my master tab is always up to date, and include all updated concepts. Below is the coding I came up with in VBA. Howev...
Lucy
1

votes
2

answer
70

Views

Record moves to a wrong row on another Excel sheet

I have a macro that moves row from one sheet to another one once I time the word 'Completed' in a certain field. The problem is that it moves row into a wrong place. I have 212 rows on another worksheet, the next row should be moved row #213 on another sheet, but it moves all the way to the row #65...
icittar
1

votes
0

answer
140

Views

VBA - WinHTTP Authentication on redmine failed

i am looking into this issue since days but i am not able to find a solution. I want to use WinHttp Authentication via VBA to Login to our Redmine to get the current issues.csv file for my Excel spreadsheets. I already found this useful question on Stackoverflow and adapted the Code(Not understandin...
ChristianR
1

votes
1

answer
45

Views

Insert new rows and also increment date inside cell

Currently I have a vba code which will duplicate rows x number of times depending on the value specific cell. It works perfectly but I need your help to improve it further. In one of my cells, I have date values and whenever I insert a row I want it to increment +1 day. Here's my current code: Sub...
Ba Troo
1

votes
2

answer
56

Views

How to tell if the string you have has a value inside the parentheses. VBA Excel

So I have these string with a parentheses and a value inside the parentheses. For example: Data(redundant) What I want to do is that the code should look at the string, for 2 conditions. If it has a 'read' or 'write' inside the parentheses. For example: Data(write) If it has something inside its par...
Link
1

votes
1

answer
276

Views

Avoid the “#value” error when using a Vlookup with Scrypting Dictionary

I am trying to use a VBA Vlookup that I found in the Question below but I keep getting the result #Value (and I am not the only one according to the comments). Question: How to optimize vlookup for high search count ? (alternatives to VLOOKUP) After putting the Function in VBA. I am using it in my...
Pierre44
1

votes
1

answer
56

Views

Script to export rows to txt-file based on value in column

I am looking for a simple script to export rows based on values in a specific cell. Has found this script (maybe it could be done a bit simpler?) but need help to filter my txt output, so that only rows with an 'x' in column B is exported. Sub Export() 'Declaring variables Dim sLine As String Dim sF...
Jesper Kindt Larsen
1

votes
1

answer
40

Views

How to make new tab on excel depending on the name, whilst adding data with queries?

I was wondering how I would go about making a macros which reads off a list of specific URL variables and makes a data query and a new sheet for a different name. For example in my url tab I have this: 1 Daniel Butler 2017 1 Daniel Butler 2018 1 Jack Riewoldt 2007 2 Jack...
user9198458
1

votes
2

answer
77

Views

Consolidating two Macros into One

I have two similar macros I've written, and for efficiency's sake I'd like to consolidate them into one. The first macro adds 4 blank rows on another tab underneath a specific row, where column C matches certain criteria. The second macro copies 4 rows of data from an existing tab over to the new ta...
1

votes
4

answer
117

Views

Copy Specific values in a column

I need some VBA help. I want to copy values in a column. Referencing to the picture, I want to start to copy when the value is 'Dept/Branch code: 144' because this is the only value is consistent and stop copying until the cell value is 'Total'. Please help!!!! I cant use range to copy because te r...
dannylee
1

votes
1

answer
52

Views

CTD in Excel VBA

My macro is causing excel to crash to desktop with no debug or error message, the whole application shuts down, closing any open instances of excel. The code that I've written so far is pretty light, so I have no idea what is causing the crash. The crash appears to occur after StartGame() has finish...
Nicolas
1

votes
1

answer
48

Views

How to create a simple help page for an Excel VBA add-in

I have created a small add-in for my client. It adds 2 buttons to the toolbar and works OK. Now I'd like to add a third button to display the version number and a small help text (15 lines). I wonder what is the easy way to do this. Options I can think of: Insert (copy) a sheet with the help & versi...
Patrick Honorez
1

votes
1

answer
68

Views

how to highlight cells over 48 hours

I am trying to highlight cells in a range which are older then 48 hours. The code I have at the moment is below and highlights the cells but when I try to unhighlight the cells that are not over 48hours, they stay highlighted. I am new to VBA code and this as boggled me after reading numerous pages....
TheRunner83
1

votes
1

answer
78

Views

convert days hrs mins secs strings to [h]:mm:ss in excel

How do I make a formula or vba that would automatically detect the days, hrs, mins and secs and convert it into the right [h]:mm:ss once I paste it into excel? I have some data need to process and they are in the following formats (string): 134 days 10 hrs 41 mins 23 secs 109 days 40 mins 37 secs 67...
Will
1

votes
0

answer
62

Views

Replace data in Word by Excel VBA

I am using this code for my work but I got a problem, when I replace number it doesn't copy format. For example: In Excel 999.999 when replace in Word 999999. Could someone help me solving this ? Sub test22() Dim num_of_row As Long Dim num_of_column As Long Dim i As Long, j As Long Dim template As O...
TLD

View additional questions