current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0

Tagged Questions

0
votes
0answers
11 views

Reading text files with specific prefix

I have a folder with lots of text files each containing (but in random order) : A = ... (new line) B = ... Now I would like to import these text files into an excel-spreadsheet with : Colum 1 = ...
0
votes
0answers
9 views

Can't open Excel 2013 file after it's been edited through Access 2013 VBA

I am working on an Access database where I have a button that, when clicked, copies a file (a template excel file) and then opens the new copy, sets the value of a couple of cells, and then saves ...
2
votes
1answer
10 views

Print multiple cell values per row in a filtered sheet

I have the following loop that iterates through a filtered excel sheet Sub SpecialLoop() Dim rng As Range Set rng = Range("A2:A11") For Each cl In rng.SpecialCells(xlCellTypeVisible) ...
0
votes
1answer
11 views

Where to place reference content for an xlam file

So I have an excel add-in that, amongst other things, contains a huge list of aliases. For example, Country names with their ISO codes, Countries with their continents, etc etc (so that I can easily ...
1
vote
1answer
15 views

How to copy Image from on workbook to another which is generated at run time

I have a workbook 'w1'. I have a code inside a button which copies the data from book 'w1' to a new book 'w2'. Here book 'w2' is generated when the button is pressed. Now I want to copy an image form ...
-4
votes
0answers
22 views

Macro to automatically select an option in a drop down list [on hold]

I have to get information from a website that updates dynamically with ajax. To get the data I'm interested in, I have to select an option from a drop-down list to populate the table. Is there a way ...
0
votes
1answer
20 views

How to lock the contents of a cell based on the contents of some other cell using VBA

I want to lock a particular range of cells say "A1:A2" based on the content of cell "B1". If the content of cell "B1" is anything other than "_" then the range "A1:A2" should get locked. Till now I ...
0
votes
1answer
16 views

todays date and future date dropdown list in a ComboBox

every time i start up the file and click the macro for the userform, the combobox should have the list of dates from today and future dates, it can not have past dates. this is the code that I have, ...
0
votes
1answer
13 views

if cell value 1 then listbox .listindex = value

I have the following code: If ws.cells(A,B).value = "1" then .ListBox1.List(.ListBox1.ListIndex,5) = Checkbox.name but it prompts an error and i'm not sure how to fix it essentially, what i'm ...
0
votes
1answer
30 views

what's the meaning of listcount -1

I've been searching on the internet trying to find what the meaning of listcount -1 is, but i can't seem to find the exact answer. why is there -1 beside the listcount? or .list(.listcount -1,1) ...
0
votes
1answer
24 views

Using collections as properties of an object in VBA

With class module "Class1Test" as Private pGreetings As Collection Public Property Get Greetings() As Collection Greetings = pGreetings End Property Public Property Let Greetings(Value As ...
0
votes
0answers
19 views

Change Font Color in Any Cells of a Column if Text is a Certain String

The following sub successfully changes the font color to red if the text in any cell in the worksheet is "Cu": Sub CuRed() 'if T-line material is Cu, then change font to red to flag ...
0
votes
1answer
13 views

VBA Send Email from Second Outlook Email Address

I am using the following code to send emails through Outlook using VBA in Excel. I have two emails addresses set up in Outlook. I'd like to send the email from my secondary email account. How would I ...
0
votes
0answers
26 views

VBA running files based on a list

Sub Update() For Each client In Range("Path").Rows Ostr = client.Value & "Auto_Update.xlsm" Workbooks.Open(Filename:= _ Ostr _ , UpdateLinks:=3).RunAutoMacros Which:=xlAutoOpen ...
0
votes
0answers
13 views

WaterMark Word VBA - problems with alignment [on hold]

im using a VBA code in a excel spreedsheet to list the files in a directory. After that, my code checks for word files in that list and open it in a word application. With that, i need to add a ...
3
votes
1answer
28 views

Difference between Cells and Range in computing average

Suppose there are 5 numbers 103.6, 104.8, 106.2 , 105.7,105.6 in cells [E2:E6]. If I run =AVERAGE over these 5 numbers, then I get 105.18. However, I do the following in the immediate window in VBA. ...
-2
votes
2answers
28 views

Compiling data from multiple worksheets to a master worksheet in a single workbook - VBA Excel Macro

Question: How can I make this macro run faster? I download data into a single workbook. The data is made up of a list of variables (string in column a) and their values (string or number in column ...
-1
votes
1answer
28 views

Excel VBA - Making a macro valid for all worksheets in workbook

I have a workbook with several sheets, (file1, file2, file3, etc), and I'm working on a macro that will parse an external file and paste the contents in the active worksheet. The issue is that I ...
0
votes
0answers
48 views

Taking custom selection to a new workbook and saving it with user inputted filename

I need to select all rows that has the same name as the address. After this, the macro creates a new workbook and saves it with the name of the address. I managed by myself to create a new workbook ...
0
votes
1answer
23 views

On manipulating date or time the format changes

This must be a cakewalk for regular VB programmer writing Excel macros. I am trying to convert negative time in -HH:-MM:-SS format to HH:MM:SS and swapping the values of the previous two ...
1
vote
1answer
20 views

Unknown Runtime Error occurs when setting a range

This is a subroutine of a larger program (I can copy and paste the whole thing if needed). I'm getting an unknown runtime error, and I can't for the life of me figure out why. I've spent a couple ...
0
votes
2answers
24 views

Getting error for : Application.Goto Workbooks(“BOOK2.XLS”).Sheets(“Sheet1”).Range(“E4:F12”)

I am getting a run time error '9' script out of range when trying to run the code Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4:F12") I am using Excel 2010. I can see many ...
0
votes
0answers
18 views

excel auto add row on sheet 2 everytime a range on sheet 1 added

sheet 1 is the current debt our clients have on us and sheet 2 is the historical transaction of our company is there a way so if everytime a row added in sheet 1 it will also be added on sheet 2 but ...
1
vote
1answer
11 views

Excel VBA UDF Formatting - Change argument values before calculation

I am trying to write a formula in Excel VBA to calculate: RR = ((A / (A + B)) / (C / (C + D))) When any of the four arguments (A, B, C, D) are 0, I want to change their value to 0.5 in the ...
0
votes
0answers
16 views

Solver in VBA does not add the integer constraint

I was using VBA code to run solver. Below is my code. I want cells C87:K93 to be integers, however, the constraint does not get added into Solver so the values I get are all decimals. May I know how I ...
0
votes
2answers
14 views

Iterate through an object HTML TableRowElement to get the innerText of one of its children

I have written the following script that, from this webpage, should be able to extract the values contained into the table field "Chg. %". Sub Test() Dim getIE As Object, appIE As Object, ...
0
votes
0answers
36 views

Replace cells with functions using cell content as argument

So this macro is supposed to 1) Find the column titled "Ingredient Cost Paid" 2) Replace all cell contents in the column by the output of a weird function (shown in the code and in the image) Note I ...
1
vote
1answer
22 views

Accidental use of built in function as variable name?

I have been writing some VBA code to produce charts automatically, and at one point named a variable "CHARTTITLE" not realizing that this is a member of the Chart object. I have deleted this variable ...
0
votes
1answer
20 views

How to copy multiple worksheets into a summary page but not merge the data.

Let me start by saying I know nothing of VB code in respect to actual programming, I'm trying to help out a friend with a workbook in Excel 2010. I did some Google searching and found what I thought ...
3
votes
2answers
30 views

VBA Msgbox - No Default Button

I would like to know if it's possile to show a Msgbox in VBA (Excel 2007), without any button being set as default? I have a yes\no button and I don't want the user to hit return or the space bar and ...
-1
votes
1answer
38 views

Compare set of cells in a row and flag in new column [on hold]

Hi I need result available in result column in MS excel. The input data is depicted below. In first row, there are 3 X columns(X1,X2,X3...) cells contains "Req" and its associated 2 Y cells(Y1, Y2, ...
-2
votes
1answer
18 views

i wanted to refresh my worksheet using vba code please let me know [on hold]

Sub downloadData() Dim strSQL As New ADODB.Connection Dim dbconn As New ADODB.Connection Dim i, prdTblName prdTblName = "tblProd_" & projCode & "_" & batchCode Dim ...
0
votes
2answers
37 views

Easy and fast way to find certain row using VBA

I'm trying to write the code that finds certain string(row) (from, for example, column B to X) in the massive of rows; row that should be found is given also in excel but another in file (doesn't ...
0
votes
0answers
24 views

“Property Let” setting not working in VBA

I am having the following problem with a VBA project I'm doing in Excel. The "Main" module is calling what follows: Sub Main() Dim webQuery As HTMLBodyScraper Set webQuery = New HTMLBodyScraper ...
-2
votes
0answers
21 views

Removes rows (based on condition) and copy cells (based on condition) [migrated]

I have to modify a huge VBA script, I am new to VBA and I must also say that I am not loving it :( I need some help writing an efficient For-loop, let me explain with an example. During the first ...
1
vote
1answer
14 views

How to add data validation to a cell using VBA

Hey I want to add "data validation" in a cell(which is variable) using VBA and the range which is to come in the data validation list is also variable. Till now I have been using this Here "range1" ...
-1
votes
0answers
30 views

Row by Row copy and pasting values

I have been working on this for awhile now. And I have been trying to achieve this step by step. but I noticed that there is too much. So far, I managed to achieve Step (1) by Autofilter with the ...
0
votes
2answers
31 views

Using excel, concatenate the first four characters in second column if present to the first four characters of data in the first column

I intending to maintain a list of VID and PID of USB devices in Microsoft SQL database. The list can be obtained from this site: http://www.linux-usb.org/usb.ids Example format of source from the ...
0
votes
0answers
12 views

Quick Analysis charts with RTD values

I have implemented an RTD server and have it successfully providing "live" data to Excel. I can work with the live data in Excel but charts don't seem to work very well. When I try to create a chart ...
3
votes
1answer
38 views

Array formula with more than 255 characters

I'm getting an error when running my VBA code. Run-time error '1004': Unable to set the FormulaArray property of the Range class I assume this is because I have more than 255 characters. If ...
0
votes
1answer
37 views

Vba Selenium: Check if html tag is present in the source code

This is my issue, I have a Vba code using selenium that loops trough a lot of urls. I need to validate if one tag is present, and if it is I need the code to resume next, the problems is that I just ...
1
vote
2answers
33 views

Add or Delete Excel Sheets Based On Array Values

I am working on a piece of code that creates an array and populates it based on the contents of a column in an Excel Sheet. I would then like to use this array to add or delete Excel Sheets. ...
0
votes
1answer
37 views

How to set reference of already opened database vba

I'm trying to get recordset for already opened database from excel vba. Get reference of opened db in daoDB variable. My excel vba code Public Sub getdatafromaccess1() Dim daoDB As DAO.Database Dim ...
0
votes
0answers
27 views

Writing access table data to excel sheet using vba , but not writing field names

I'm trying to write access table data to excel sheet. But no field names are writing to excel. My excel vba code Private Sub Cal_Click() Dim daoDB As DAO.Database Dim daoQueryDef As DAO.QueryDef Dim ...
-1
votes
0answers
24 views

VBA 64-bit Office Unexpected Crashes

I'm trying to convert our VB macros from 32-bit (Compatibility mode) to a 64-bit office running on Windows 7 64 bit. However, the macros have been behaving very unexpectedly. It's a fairly big macro ...
0
votes
0answers
36 views

Getting excel file path from access vba

I'm trying to get excel file path from access vba while desired excel work book is closed. I've written function in excel and calling from access Excel Method Public Sub writexlpath(flpath As ...
0
votes
0answers
21 views

Excel VBA paste data to column based on criteria

I have found some code that will enable me to select and copy specific data (column C) Sheet1 in a row based on criteria (Date) in column A, then paste into a different sheet (Sheet2). What I would ...
0
votes
1answer
28 views

How to create excel VBA change log

I am trying to write a change log for excel VBA. I want it to iterate through so that the each additional response is populated in the workbook as the additional rows. Please let me know if you have ...
0
votes
2answers
64 views

What is the C# equivalent of this VBA code?

I have this VBA code that refreshes the data connections on an excel sheet, and I am updating the code a bit and like the reliability of C# as opposed to Visual Basic... So my question is what is the ...
0
votes
2answers
20 views

VBA for loop; if cells are in range, insert range value

I am struggling trying to make this work. I have a list of dollar amounts, and I need to loop through them and identify those which fall into certain ranges. Current Data Name Value Range ...

1 2 3 4 5 … 168