Questions tagged [excel-vba]

26493 questions
1

votes
2

answer
12

Views

Use Font.Color as Condition in If Statement

I am currently trying to use the font color as a condition to check against in an if statement. It seems incredibly easy, but VBA doesn't seem to be able to do it. I can't show my actual code as it has proprietary information in it, but I have even tried a simple version of my code with no avail....
B. Moore
1

votes
1

answer
4.8k

Views

Macro to save only the active worksheet

In the below code it saves the complete workbook. I want to save only the active worksheet. Sub sbVBS_To_SAVE_ActiveWorkbook() ActiveWorkbook.Save End Sub
1

votes
2

answer
49

Views

If user skips rows to input a numeric value, force user to type in data in the following available row

I have a sheet and the user input Starts in A7 and calculations are inserted with a macro to B7 all the way through I. I got all that worked out but what i need is: the user is going down typing in data in Column A, lets say they input data Starting from A7, through A11, then skip A12 and type int...
1

votes
2

answer
436

Views

Excel - Pull data from a cell on a different sheet and hyperlink

I know how to pull data from a separate worksheet in excel by using the formula =Sheet2!A2. That pulls whatever data I have in the A2 cell from my Sheet2. But is there away to pull the data from that cell and hyperlink it to the cell by using a formula? I know you can manually link it but I'm trying...
Cameron Cole
1

votes
3

answer
582

Views

Returning a column number from a table using the =COLUMN function

I have the following function in Excel that returns the column number of a table based on the column header. The table is called Config and the table column header is value. The below will return to me the column number in excel. =COLUMN(Config[Value])-COLUMN(Config)+1 Could anybody let me know how...
Geminiflipflop
1

votes
5

answer
8.3k

Views

Function for detecting duplicates in Excel sheet

I need a function that can detect duplicates in a specified Excel column. I have this one but it does not work properly. It cannot distinguish between the value '46.500' and the value '46.5000'. The countif function probably compares cells as numbers. These cells are formatted as text and I have eve...
Plasmuska
0

votes
0

answer
7

Views

how to fix “Copy method of worksheet class failed” error?

Iam getting error as copy method of worksheet class failed 1004 while compiling the below program. Sub Macro1() Dim master As Workbook, awb As Workbook Dim s1 As String Dim s2 As String Dim i As Long, N As Integer N = 3 Set master = ThisWorkbook s1 = 'C:\Users\Desktop\Tes...
Gokula krishnan
-1

votes
2

answer
19

Views

Is it possible to modify a macro code using another macro in Excel VBA

Actually I have written a same macro for multiple workbooks which does the same work, the growth of the workbook become too longer from start to now i.e. it's nearly 700+ as of now, so if I need to modify a code slightly in macro, I need to open and update in all the workbook manually so is there an...
vetha
0

votes
0

answer
6

Views

Preciso de uma ajuda em um código de VBA

Preciso de um código para avaliar duas colunas, uma coluna com uma quantidade de um documento de venda que será zero ou maior que zero e outra com um documento de vendas onde pode haver ordens duplicadas. Assim a ideia seria avaliar os documentos de vendas duplicados que estarão em sequencia e de...
David Cardoso
1

votes
3

answer
15.9k

Views

How to change file extensions in VBA

I feel like this must be simple, but I can't find the answer. I'm saving a bunch of csv files using vba and would like to change all the file extensions from .csv to .txt to import into another program (Revit) which only recognizes the .txt extension. Is this possible? Here is the command I'm usi...
user1707675
0

votes
0

answer
17

Views

Input text to search box on web page and click Search

I am trying to input data from Excel workbook to web pages search field and click search button. A lot of different approaches has been tested with success. Current solution gives 424 Error, Object required. Pointing to: .Document.getElementById('crmGrid_findCriteria').Value = _ Sheets('Other data')...
user7202022
0

votes
1

answer
18

Views

Find is not finding the first occurrence of text in second row

Here is my excel data I am trying to find the cell data in row 1 and 2 arrCols = Array('APPLE', 'ORANGE') With Sheet1 For i = LBound(arrCols) To UBound(arrCols) Set colHeader = .Rows(1).Find(arrCols(i), , xlValues, xlWhole, xlByColumns, xlNext, False) Debug.Print arrCols(i) & '=' & colHeader.Address...
Pankaj Jaju
1

votes
2

answer
618

Views

Type mismatch error while setting custom number format for particular range in excel vba

I'm getting error while setting custom number format to the particular range in worksheet. i have tried many combination but unable to find exact solution.. my code is : ws1.Range('C2: C' & p).NumberFormat = '_($* #,##0.00_);_($* (#,##0.00);_($* ' - '??_);_(@_)' 'Number format accounting how to reso...
vivek padelkar
1

votes
2

answer
133

Views

VBA to delete without warning any sheet for which the name contains the word “sheet”

I have a small piece of VBA code here which Deletes the worksheet called “Sheet1” Without Warning. What I would like that my code do is to delete without warning any worksheet containing in its name the word “Sheet” (for example: 1000Sheet, 10Sheet,Sheet2,Sheet3…Sheet1000…) Thanks a lot...
Xavi
1

votes
1

answer
50

Views

Value Error when using UBound for ParamArray

I am trying to write a function that calculates the minimum distance of one zip code to the other. The function should take in the longitude and latitude of one zip code, and then a 2-D array with all longitude and latitude info of zip codes. Here is the function I wrote: Public Function PassArray(...
Yatong
1

votes
2

answer
44

Views

How to find a cell that contains parenthesis around a number - e.g. (1)

The formula I'm using is: =IF(SUM(COUNTIF(K6,'*'&{'current','(1)'}&'*')),'within 5 minutes', IF(SUM(COUNTIF(K6,'*'&{'current','(2)'}&'*')),'within 10 minutes', IF(SUM(COUNTIF(K6,'*'&{'current','(3)'}&'*')),'within 15 minutes', IF(SUM(COUNTIF(K6,'*'&{'current','(4)'}&'*')),'within 20 minutes', IF(SUM...
max
1

votes
3

answer
61

Views

Excel VBA - How to do Loop for Copying Data following blank cells in a column and paste it to last empty column?

I am working on a spreadsheet where I need to copy a block of data in column A (which are in between blank spaces) and paste it to last empty column. Example: I have data in range A1:A18 and a blank cell, and again data in A20:A37 and 2 blank cells & and again data in A40:A57 & so on. I need to copy...
Bigyan Bhandari
0

votes
0

answer
11

Views

Why the code doesn't click in the button?

I'm trying to open a IE window and click in the button to automate a repetitive work. The code opens the window but don't click in the button, any help? The button have this code:
Stackuser
0

votes
2

answer
14

Views

Assign cell values base don Range variable

I currently have code that copies values from several ranges, and pastes those values into other ranges. The scope of this project is constantly changing, so the ranges need to be changed in my VBA code every time a row or column is added. I'm trying to streamline this by creating Global range varia...
Alex D
0

votes
0

answer
5

Views

How to use Vlookup in Excel VBA to compare values between two columns not located on the same sheet

So I'm using Excel vba to perform a Vlookup that compares a specific column between two sheets. I dont want to directly reference the sheet names in my vlookup function since one of the sheet along with he name changes each time I run my vba vlookup code . Therefore, I have a cell containing the upd...
Rehan
0

votes
0

answer
3

Views

How to use search and update buttons on a locked worksheet

I have entered code for adding data to a locked worksheet, but the search and update commands do not work when trying to change data using the userform. I have managed to find how to add data but not how to make it work for search and update Private Sub Workbook_Open() Sheet1.Protect Password:='xyz'...
Tricia
1

votes
1

answer
48.9k

Views

.End(xlToRight) within a range

I've just got a quick one wondering if I can somehow alter the following snippet to include .End(xlToRight) instead of defining L6 (the result is the same). Sub Test() Dim LastCol As String With Worksheets('Sheet1') LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Address .Range(Range('A5'), LastCo...
heavyarms
0

votes
1

answer
9

Views

How to use Application.match?

I am new to Excel VBA. I have these random columns: Range('Y1').Value = 'LITIGATE_PERSON_ADDRESS' Range('Z1').Value = 'LITIGATE_PERSON_CITY' Range('AA1').Value = 'LITIGATE_PERSON_TK' Range('AB1').Value = 'LITIGATE_PERSON_ADDRESS_TYPE' Here's the part of code that is being used for these columns. Wor...
Manos Alexopoulos
1

votes
4

answer
11.7k

Views

Error from VB excel macro code - msxml3.dll -2146697211 The system cannot locate the resource specified

I am using an MSXML.HTTPRequest object in a VB macro (excel) to communicate with an HTTP server. The problem is that it throws the following error intermittently. msxml3.dll -2146697211 The system cannot locate the resource specified. I noticed on my system that this happened when the network was do...
nutsiepully
1

votes
2

answer
5.9k

Views

Can't close Excel App with vba in a scheduled task

I have made a code in vba which works with different Workbooks and Worksheets. This code must be execute in a scheduled task. But for an unknow reason I have a problem with it : When I execute it manually, it works fine and excel closes itself. But with my scheduled task, Excel closes all Workbooks...
Adrien A.
1

votes
3

answer
6.7k

Views

Can we reset For loop counter in VBScript?

I have an Array say: VMHArray = (12,22,34,4) Now have another Arraylist object say ArrayListTaskDetails holding the data as (12,55,44,4,12,22,21,107,43,22,34) Now the below code I wrote to remove items from the list ArrayListTaskDetails which are not present in the VMHArray. Code Dim Flag : Flag = t...
CodeLover
1

votes
2

answer
4k

Views

How to change RefersTo for named range?

I have a class ValidationChanger, with a method changeNamedRangeAddress that should change the RefersTo address for a named range. However, my code is unexpectedly wrapping the new address in double quotes. Class definition for ValidationChanger here: 'ValidationChanger Sub changeNamedRangeAddress(...
sigil
0

votes
0

answer
5

Views

extract text from Secured PDF

I want to extract data from secured pdf using vba. The pdf consist of password and after inserting password it shows pdf as secured pdf. I am able to open the password protected pdf but while converting pdf to txt format it does not copy the data from pdf to text due to the secured version
sunny
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
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
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
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
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
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
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

View additional questions