Newest 'excel-vba' Questions

Q&A for professional and enthusiast programmers

Excel VBA - Use array as criteria in autofilter, not expected results

The ultimate goal of this procedure is to delete all rows in the "TestData" table that have a user which is listed in the "SysAccts" table (on the "Lists" worksheet). I'm using autofilter to find ...

How can I send a range in the body of an email using a macro (mac)? (VBA)

I'm trying to send a range from a spreadsheet in the body of an email. Using excel 2011, OSX El Capitan and mac mail. Using http://www.rondebruin.nl/mac/macmail/macmail1.htm I can send the spreadsheet ...

VBA UDF to find multi column criteria match using dynamic arrays

I posted a question in regards to finding a match with multi-column criteria. The provided answer works great. But I'm trying to make it a universal solution for my project, in terms of how many ...

VBA IE.document Click

The HTML code is as followed on a webpage. It's a link that I want to be able to click on from VBA. <a title="View Account Summary" class="unregLink" href="/Standard/AccountSummary/Pages/...

Excel formula returns #REF when linking to external worksheets and copied

I have a spreadsheet October2016.xls with formulas for external links to other sheets. When I copy the sheets to a different directory, I will get a #REF error. For example, I need to link to Assembly ...

creating charts with vba script

I am writing a script to create charts, one per line in my table. The idea is to get a line graph to show the history. the table looks like this: date modem capacity used 16/01/2017 PUT ...

how to get condition looked at before triggering a selectionchange event in excel vba

I am looking for a way to trigger a SelectionChange event on a specific Target.Address only if another cell on the same worksheet is not empty. the code I am using is this one: Private Sub ...

Excel vba to search mail in outlook

I have below code to search a mail with specific subject in Outlook But Below code searched mail only in Inbox. But I want to search mail in all folder and subfolder. Also I wants to search latest ...

Add Records to Access Web App from Excel via VBA

I have an Excel Macro which extracts data from another file, then reformats said data, and finally adds said data to an Access database file as new records. My script works perfectly with regards to a ...

VBA Code does not always save in desktop

I have a piece of code in most cases will save to the desktop, but sometimes when I am connected to the network, it will save into a network folder. I am keen for this to always save it in my desktop ...

CountIf not automatically calculating

My countif function if not calculating automatically - am I missing something? Option Explicit Function my3CountIfs(Rng1 As Range, Criteria1 As String, Rng2 As Range, Criteria2 As String, Rng3 ...

VBA Code to copy specific header name columns and paste it another wb

I have code to select source wb and then copy ws to Main WB. I want to change code for selecting source wb & then ws, then code find special header name and copy entire specific columns data form ...

Formula for getting worksheet names based on pivot table results

I have an excel file with 30 time cards, each on their own worksheet, where the only identifier is the worksheet name (ie the employee name). Each worksheet has a first column of account numbers, ...

vba , Autofill userform from another workbook cell selection

I have a workbook that used to be one with many sheets, I have now split the sheet to different workbooks. The problem I now have is userform population from cell selection. When all the sheets were ...

Check if BuiltInDocumentProperty is set without error trapping

Task: My goal is to check if a value has been set in the BuiltInDocumentProperties collection of an Excel workbook. Amplifying remark: I know that some doc properties items never show a value in ...

How to create a dynamic list in VBA?

I have an excel sheet. I want to create a list which will be in 5 cells. For simplicity, lets call the items in the list as (item1, item2, item3, item4, item5). If I select "list1" from a cell-1, the ...

Using Option Buttons on a UserForm to place text in a range from the sub calling the UserForm

I apologize if this is posted elsewhere, but I am having issues understanding the relationship between the sub calling the user form and the user form controls. I have a sub that will populate data ...

Excel vba slow execution in big data worksheet

The code below is really slow my computer is taking some time to complete the action. I tried to use a fewer lines from author_metadata but even 40000 lines is too many. Is there a faster alternative ...

Modify XML File and Save As New using Excel VBA

May I start off by stating that I'm an absolute novice when it comes to VBA. There is an XML file with various numbers in it. Can I use VBA in such a way where these values are replaced with those ...

Detect and Click on a javascript alert with vba

Hi Really need some help here. I am trying to go thru a webscrap results from a website and only problem is when I input an invalid input it throws an java alert box which i am not able to overcome ...

VBA search for repeating single character using a Regex

So I've been learning VBA this past week and I have coded a database cleaning tool. However I am trying to Implement a clean that removes any signs of a repeating character. Specifically xxx, TTTTT, ...

How to get images from web Multiple URL (website url.html)

How to get images from web Multiple URL (website url.html)

Count If A or B but with no doublecounting

I have a group of cells that have strings of indicating various statements. I need it to count the cells uniquely if it has (For example) ABC, ABD, or ABQ however one cell could read " ABC ABD AMN" ...

How to add values in excel sheet through microsoft access and choosing specific sheet

I have an excel file with many different sheets. I was wondering how I can open the workbook, and input a value into a specific cell and choosing which sheet it goes in using visual basic (Microsoft ...

Get excel to create groups of data based on cell values

I have a question that has me stumped. I feel like I am very close to the answer, but cant ever create an adequate solution. I have tried nested IF statements, incorporating the AND function, ...

Counting the number of associated value in the same row using a specific cell value in Excel-VBA

I am dealing with 8700 or more random values is a row. Now I am having problem in identifying the associated value of each row and the number of times it is associated with, since there's just too ...

Replace all in workbook with value from next column

Can you find and replace using text from the next column over. I want to Find everything in column "B" on the Master Item List Sheet and Replace it with the value in column "E", Also replacing it on ...

Excel VBA find and replace between sheets and generate a word file [on hold]

To replace set of texts from sheet2 with values from specific cells from sheet1. how do i get this using excel VBA?

Copy rows based on cell value and paste on a new sheet

Check This I need a help. I want to copy whole cell from A sheet name "Components" only if value in Column C is > 0 to a new Sheet name "Load list" Can someone please give me the macro code for this?...

IsInArray Function VBA

I found a function that returns me a true/false result if a certain string, number or date is in an array. I slightly modified it so it gives me a number instead (if the value is in the array, it ...

run a vba macro in a workbook from another workbook

I am trying to write a macroB in WorkbookB which opens another WorkbookA and runs macroA in sheet1. I googled and found two methods: Using Application.Run Dim i As Integer, en As Integer en = Range(...

Split text in cells at commas

Let's assume a simple Excel spreadsheet with two columns A and B, where in the B column there are comma separated values. I need a VBA function to split these values each one in new rows starting from ...

VBA modeless form stops Function Keys

I'd like to have my own help file in my Excel program. In the Private Sub Workbook_Open() I have Application.OnKey "{F1}", "Help" That works when I'm on the Excel sheet but my application is ...

VBA to concatenate and then increment resulting value based on another value

I am trying to write a macro to loop through a spreadsheet, and on each row, concatenate three values (from different columns) into a single value (integer), and result that in another column on the ...

Unable to get reference to internet explorer document object vba

I want to be able to access internet explorer using vba Dim ie As InternetExplorer Dim i As IHTMLDocument Dim d As HTMLDocument Set ie = New InternetExplorer ie.Visible = True ie.Navigate "www.google....

How can I convert a range to a string (VBA)?

What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of the formatting ...

Saving Excel workbook on a local network location

I am trying to save a workbook on a local network drive location. the code is working fine, the only thing is, an alert shows up showing file being saved and the network path is shown on the same. ...

I have an Excel sheet that uses many form checkboxes. Is there a way to deselect all checkboxes at once?

I have an Excel document with 62 sheets. Each sheet has numerous form control checkboxes. Is there a way to clear these checkboxes all at once?

How can I use VBA to load contents into a cell comment?

I have a spreadsheet that I will update monthly that tracks the percentage of applications approved. I want to be able to hover over a cell and see, in a comment, data relevent to the contents of that ...

excel vba function ro read userform elements value

I need a function or subroutine that returns the value from a cell or a UserForm element by using a com like call inside the cells. In column A I have Sheet Name, in column C i have a cell address ...

VBA HostExplorer Interaction

So i'm working with a new mainframe at my job and it's horrible. I don't need to get into specifics but whoever created it is a horrible human being... So for the past few weeks I've been coming up ...

Picking value from a cell inside for loop VBA

Sub asd() a = 34 For x = 1 To 4 **prev = Sheets("Sheet1").Cells(1, x - 1).Value** this = Sheets("Sheet1").Cells(1, x).Value **nex = Sheets("Sheet1").Cells(1, x + 1).Value** If WorksheetFunction....

Need help to advice how to solve using Excel VBA

I have 2 tables as shown below Table 1 AA BB CC DD EE Table 2 bb aa bb1 bb2 cc1 cc2 cc3 I need help to do the below steps using Excel VBA ...

Worksheet Function in Excel Vba with concatenated values

I have the following formula: dim functionString as String functionString = "IFERROR(AND(MATCH(" & FirstName.Value & ",C2:C1048576, 0)>0, (MATCH(" & LastName.Value & ",B2:...

VBA - Auto format wrapped cells so the width of the cell is equal to the width of the longest word

I would like to set the width of each column so that no individual word in the wrapped text cell is split into 2 lines. Excel handle this for words less wide than the width of the column. However, ...

I Want to alter the export layout of a quick books report export

I have a report that I exported from Quick Books POS. The problem is the layout does not work for me to perform the analysis I want to do. See Picture: I want to alter that layout to the following ...

Excel Userform VBA VLOOKUP from intranet file

I have a VBA Vlookup on a user form from which I would like the data to come from an excel file saved on the intranet. When I try the VLookup which takes the data on the intranet, it works as ...

Copy a static named range and paste it to a variable cell adress in another worksheet

In Worksheet A, I have a single column of meter readings with a static named range "MeterData". The first row of the range is the date (mm/dd/yy) when the meters were read. The remaining rows contain ...

How to write a VBA function whose return value is recognized as a date by Excel?

I wrote the following test function in MS Excel 2002: Function someDate() As Date someDate = Now() End Function Then I use the function in a cell, like this =someDate(). But the value is shown ...

Enter data horizontally in Excel from SQL query

Currently the following SQL query in Excel VBA shows the count data vertically So the column header is in A1 and the count result is in B1 Const sqlconnection = "Provider=visoledb;" Dim conn As New ...
Translating... 0%