Hot Solutions

The difference between WorksheetFunction and Application is that WorksheetFunction provides intellisence but can cause random resolution problems (the function referenced might not be found or resolved.)

Why?

Anthony

Hi

I am receiving an error message when opening some excel files. This is :- "Excel cannot complete this task with available resources. Choose less data or close other applications"

No other applications are open. I have tried the basics...

Hi Experts,

I have Excel 2003, 2007 and 2010 and tried to set up Virtual Machines for the two earlier versions. Using Windows XPMode in Windows 7 Ultimate, I first created a Virtual Machine and did all the Windows Updates, closed the VM and...

On a Results tab, cell B4 is the sum of several cells from other worksheets.   I want to update cell D4 on the Results tab with the current date / time when the value changes in cell B4.  A formula solution is preferred but can use a VBA solution...

Hello Experts!

I have a spreadsheet that has many rows of call center data.  Here is my setup...

"Sheet1" = contains all the rows of data.
"Sheet2" = contains the lookup table.
...

Does anyone know if it's possible to use code to generate a full list of all the constants in Excel?

Hello Experts,

I have color information taken from VB.Net ColorDialog and saved in  SQL table as ARGB integer values.

For example:
Black = -16777216
White = -1
Red = -65536
Blue= -16776961

In VB.Net application, I can apply color as...

Using a new Windows 7 machine where we are limited to using Visio 2003 Standard as a Virtual Application.  Using an Excel VBA document, which needs to open a Visio document from the VBA code.

From inside Excel VBA, this was my latest attempt:...

I use lookup function in a excel file with 34000 files to search the ones that aply to my criteria (e2):

=SI.ERROR(BUSCARV(E2;'dades n-1'!F2:$J$34000;2;false);0)

Buscarv is lookup in spanish

The problem is that i do the same operation,...

I have developed a VB6 application that generates an Excel spreadsheet.

One of the columns contains email addresses. User would like to copy and paste a selection from that column directly into the "TO" box of his email utility (which happens...

I am using a macro, that I wrote, to open a bunch of files (on a shared folder, on files that other people may be using as well), copy worksheets into a new workbook, and then close those files (without saving).

I then do work on the new...

I have space available numbers in a spreadsheet tracked over time. I also have space used numbers. I need to know how to calculate two things:

1. Given growth patterns over time, when will we run out of space?
2. Given same numbers, when...

Hi Guys, what would be the code for

If Range(S6:S450) >=38.75 then VB red, else VB white

If (S6:S450) >=38.75 then Range(B6:B450)VB red, else VB white

I need the code to activate these 2 columns depending on Column S values

I did this...

("The Question" is at the end)
-------------------------------------

I use the term "banks" to include all sources such as credit cards and building societies.

I am a practicing accountant (which is relevant in this question as it provides...

I have a macro that I have create and would like to add it to my Quick Access Toolbar?  Is this possible in Excel 2007.
thanks

I receive a daily spreadsheet (see attached - Received tab) where some of the cells have a combination of a column header and the headers value all in once cell.  There are cells that contain the header called "Rep" along with the Sales Rep name...

I have an Excel 2010 spreadsheet with 242 unique rows in column A (a list of printer names)

I have a second spreadsheet with 130 rows, where a row in column A may contain multiple printer names, but column B contains unique IP Addresses.

So...

My app creates an Excel spreadsheet.
Column G contains email addresses.
I would like those to appear as "live", blue and underlined.
Here is a fragment of my code. Doesn't work. Please help.
 

Hi,
One of our users updated their PC yesterday and after the reboot everytime he opens excel he gets the following error message with the title Micrososft Visual Basic;
"Compile error in Hidden module: modMain"
I have read that it's an Adobe...

Hi,

I have two tabs Sheet1 and Sheet2.  On Sheet 2 in cells A2 to A1000, there are Text values.  On Sheet1 I want a formula that will randomly pick one of the values from that range on Sheet2 and put it in a cell.  I can do a @Rand on numbers...

How can I find the max character length in columns B & C in the attached file?  What I'm trying to determine is if any of the records exceed 10 characters in length.

So, for column "B" do any of the record values exceed 10 characters?
and,...

Please can you help me with a formula for sheet 2 C & D 12

I want to count the number of cells in sheet 1 column Y, which are over/under 48:00 (HH:MM)

However I want the formula to be dependent upon the date shown in C4:C5 sheet 2.

I have a list of addresses in excel where there are some shown as 1-3 or 2-4 but they appear as 1 Mar or 2 Apr.

Is there a way to make them appear as numbers without amending each cell by adding a space before each number?

Hello All,

I am facing a issue working with xlsm. I was to simply insert a shape object to format the checkboxes into groups. Unfortunately, whenever I add the shape object and then close and save the xlsm file, the next time I open it I get...

Hi,

I need a piece of code that counts Col A as its reference for how many rows to loop through and then starts looping down.

If it finds that there are rows which have the same value in both columns F&G, then it will highlight those rows...

Hi Experts, I have been given an assignment of taking a list of addresses and determining the lat/lon for them.  I'm at a bit of a loss as to how this can be accomplished.  I did see a previously entered note here which did the job in HTML for a...

Hi experts!
I have an report file that comes with merged cells in a format I'm not sure of.
Is it possible to separate the info into individual cells? The thing is I want to be able to use filter and so on.  I attached the file and marked the...

Hello all

I'm trying to clear all cells in row if in column 12 =  "Total"

I dont want to delete de row, just clear all cells in that row.

How can i do that?

Thanks again for your help

 

We currently are using Excel to create line graphs of data.  We want to be able to have the data points on the graph to have optional labels.  In our case if the datapoint has an exceptional circumstance we want to have put a note in a columnn...

I spent a couple hours trying to tackle this issue, but just couldn't work it out.  Not a lot of help in Google-land, either.

I'd like to think there was already an algorithm that would automate the placement of datalabels on a chart, to avoid...

Hi. In the following procedure that I found in an example of using classes I am trying to cause an error so that the class code below auotmatically protects a worksheet that my code has unprotected, but this doesn't happen as Class_Terminate...

Hi,

I am using VBA to control worksheet event (please see vba in attached file)

However, the VBA is not locking/unlocking cell F12 when a choice is selected from F8.

Any help is appreicated on why this is not locking. Password is 1234
...

I have a HUGE Excel VBA Macro that was written under Windows. It reads in a CSV file,  changes the content, then writes out a new CSV. Sometimes it reads in .JPG files just to check if they exist. No databases.
NOW I want to run it on a Mac...

Hi

I have gathered that a class eg Employee can have properties eg Employee.Address and Employee.Number

So Once I instantiate a new Employee I can assign properties
eg Employee.Address = "31 Crescent Way....

but what is the use of this...

Hello,

I am trying to figure out how to auto-generate values for Column B in the attached spreadsheet. I have manually populated the first several rows with the correct value.

Pseudo-code for populating Column B:
For each row:
1) Look up...

I have just upgraded from Office 2003 to Office 2010.

Previously whenever I closed an unsaved file in Word or Excel I would hear a 'ding' when the dialogue box came up asking me if I wanted to save the file.

Now there is no sound.  

How...

I have a user that when they edit this one excel file a .tmp file is created everytime. The fle is located on a windows 2003 file share... The user is using a Windows 7 Office 2007 pc. I tried removing Excel Add-ons and also turing off indexing...

Hi All,

I have a function that deletes records from a table based on the records selected in a list box control on a user form.

Enclosed are two functions, one (collectDeleteRDCArray) that collects the data for deletion from a listbox...

How can we change the appearance of hyperlinked text so that it is not different from the rest of the text? Or is this not possible?

What's the difference between the Analysis Toolpak and the Analysis Toolpak VBA? See graphic:  

I have a workbook with multiple worksheets and I would like a VBA script that will bring up a userform with all the visable sheets on with a check box next to each one. I would then like to be able to tick each sheet that I want to publish to a...

Hello,

I have an excel file that needs the address separated, Basically i need the zip and State and city  removed from the column and each need to be in its own column. As you can see in the attached example i don't really have a consistent...

This might get a little confusing, so I hope that I do a good job of explaining myself. I have scaled this example down, but in my project there will be between 20,000 and 100,000 to evaluate. I have tried most of the day with Index, vlookup and...

I am doing a excel on counting, like:

col A                        colB
AgentA                   AgentAwater
AgentB                   AgentBpaper
AgentA                   AgentAsea
AgentA                   AgentAwater
AgentB              ...

EE Professionals,

I have a WS that has two distinct sections on it (One starting in A1 and the other in J42).  I've place a "Go To" and a "Return" button on the WS.  The Return button specifies cell B2 and when triggered, it shows the...

In the attached spreadsheet, could I please have the macro amended so that I have commas in the appropiate output columns where there are numbers please?

Thanks.

I would like to have a VBA code write the formula

="SUMMARY FOR "&TEXT(TradeDate,"MMMM D, YYYY")

into Cell A1 as the document title.

However, I have not been successful yet with the following approach:
 

What is the correct syntax to...

Hi Experts,

How to make the attached script able to convert date and time according to the date sequence after adding 8 hours (+8). Currently the script unable to convert the date after 00:00 hours. I highlighted (yellow) the discrepancy of...

I want to write this code (which works):

 

in this following form in order to do the evaluate only once:

 




I was originally trying to do this:


         EvalVar = Evaluate(Cell.Value)

                    If CLng(EvalVar)...

=INDEX(Sheet8:Sheet1300!$A$2:$E$3,,MATCH(B9,Sheet8:Sheet1300!$A$1:$E$1,0))
=HLOOKUP(B9,Sheet7:Sheet1300!$B$1:$F$6,2,0)
I have 1300 sheets and one sheet with phone numbers in the B column on sheet7
In B1of all the sheets i have a phone number...

 

Hi,

Above is partial code of my worksheet change event. What happens is, a user enters dimensions in 3 boxes and volume is calculated in another box called N30

I want to blink certain cells everytime the cell N30 changes (it doesnt...

Need Experts help create a macro to highlight cell at column M, if the data is back to back. E.g. data at cell M3 and M4 are having the same data back to back, therefore these two cells are highlighted in yellow. Similar situation at cell M10 and...

Hi, I have the following 2 columns in an excel sheet

 

code is simple, highlight if Column A has a weekend date in it

 

now the problem, it currently selects 1 row above the correct row
e.g.
12/12/2011       
12/12/2011       Highlighted,...

I am moving data from one program to another program. this involves exporting the data to a CSv file. Then I open the CSv file with excel. then I edit the data. then I resave as CSv file and import the data to the new program. when I export the...

I think this is self explanatory, I want to array enter and it's not working, possibly because the udf doesn't like it? In any case prefer to use non array such as sum product.

The UDF allows the array constant to be read as input, otherwise...

Hello Experts,

I'm looking for a formula to remove "01-01-1900" from a time value like this:
"01-01-1900  01:00:00"

The end result after applying the formula should be "01:00:00"

Thank you,
Hani

I have a sumifs statement that sums all of the authorizations if the the code is column C is equal to the reason code and the State is equal "NJ"

SUMIFS(auths,State,"NJ",Rsn,$C21)

The challenge I have is that there are some outliers that I...

what is the vba code that actual goes to the bottom of the workbook and works its way back up to count the number of used rows.  for instance you have a value in a1 and a5 and the code will return a count of 5 by going to the bottom and then...

Hi Experts

I am looking for a solution for something that seems very simple on the surface but I am not sure if it really is in reality.  

Here's the challenge:
              A      B      C      D      E      F      G      H     I      J ...

Dear Experts,
I need an example which will facilitate the programming of t-Statistics for the various regression coefficients in multiple regression.  The specific part of this task that I am having trouble doing is calculating the standard...

Hi everyone.
1 How to create chartType xlLineMarkers in Excel VBA that has:
Title on left but positioned above the graph,
Subtitles positioned on right but above the chart,
Title X-axis centered horizontally and below the graph and not...

Hello Experts,

I’m looking for VBA code for handling a complex formatting procedure for a table in an Excel sheet.

The table is originally based on Pivot Table output and is about weekly ratings of TV shows as provided by two suppliers (IPS...

Hello,

I would really appreciate someones help with a tricky little problem i'm having.

I am linking over 20 Excel charts from the same spreadsheet into a PowerPoint 2007 presentation.  It all works very well until I close the presentation...

Hi,

There are three tables in excel:
The Accountlist table lists all the account codes with the type of price category they are assigned to.
The PriceList item list lists out all merchandise with their corresponding prices.
Ledger lists...

I have some code that will round a number to a significant figure. What I now need to do is create a code that will truncate a number to a significant figure.

i.e. Truncate to 8 significant figures;

0.0000123456789

becomes
...

Hi. I am trying to apply a theme to my PowerPoint 2007 presentation using the following code

    oPresentation.ApplyTheme("C:\Program Files\Microsoft Office\Templates\Apex.pot")

I can't seem to find where the themes reside on my system. Is...

Hi

I have a button with a flatstyle property set to "popup". What is the best way to change the back color to dark orange on the mouse hover. If I use the FlatAppearance>MouseOverBackColor property it doesn't change. Should I just use the...

In Excel 2010, how to put a button with the Macro in menu bar so that it is always available?
Thank you,
ocaccy

I have 2 macros that work fine on my PC (thanks to jigneshtahr who helped get one working)...
 
The problem one copies a sheet from each workbook in a folder and pastes values to a new sheet in a new workbook (consolidates 50 Budget sheets from...

Hi,

I have the following code in my VBA program which try to find the column location of a currency code in row of my current spreadsheet.

Code are as follow:

Dim CNYcol As Variant
CNYcol = WorksheetFunction.Match("CNY", Rows(2), 0)
...

Hello Experts,

I'm trying to build a dynamic data entry sheet where the number of feilds available for data entry is dependent on a number entered by the user himself.

The attached sheet will explain the problem in a much better way.
...

Hi,

I'm about to change the folder and file structure on one of my company's fileservers. I have the new folder structure in place, but in order for me and the business to determine where to move the existing files and folder in the two or...

Change XLS file on http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27611512.html so it automatically prints to my "Canon_Duplex" printer instead of my Windows Default "Sharp" printer.

File is also attached.

Hi Experts!

Attached is some code I'm using to Filter one column in a Table and then delete the visible rows.  I had difficulty with Selection.EntireRow.Delete but have replaced it with RngToDelete.Delete.

My problem is that the code stops...

Can some show me how to modify the hyperlink to show the active cell at the top of the screen? Attached is a practise worksheet with the hyper links working. The only problem is when I'm calling for "Cabbage" it is displyed at the bottom of the...

I want to give effect to this:

I would also use Variants here since it will always save you having to do two calculations and possibly three. Further, I would load the entire range into a Variant to start with rather than reading cell by cell....

I have a spreadsheet with 2 fields, with date and time. See below

Severity               Closed.TIME              Re-Assigned.TIME      
Sev 3              2/2/2012 11:41      1/31/2012 16:09      
Work Order      2/13/2012 10:07      12/22/2011 15:46      
Work Order      3/5/2012 11:14...

We are just starting to migrate to Windows 7 and Office 2010 and have come across the following problem with Excel/Outlook 2010.

In Excel if I go to File > Save and Send > Send Using E-mail > Send as Attachment
I get the following error...

Hi:

I have a group of adjacent cells (e.g., A2, B2, C2, D2 on the same row)  in Excel 2007/2010. Only one should be filled. Is there a way to give a visual cue to the user indicating that the input is invalid until at least one cell in the set...

I need to set a value in a cell based on which cell has a "x". I have a total of 7 cells and only one can have a "x", based on which one it is I need to associate a value. I came up with the formula below that works but dont want to display...

The following runs the vlookup twice.
Is it possible to run the lookup once, put the result in  a variable, then test the variable for error and skip it if error and add it to sumlookups (in this case) if not error?


 

anthony

Hi I have two lists of information in Excel, I need to do some kind of lookup or report that will compare 2 values in each list and give me a 3rd Value.
e.g.


List 1
Port Name          Device Name          Switch Name
Port 1             ...

When we exit an Excel that has VBA in it, an instance stays in memory and starts taking resources making the PC slow.

How can solve this problem so when the Excel is exited, it removes itself from memory.

Note: only happens with an Excel...

Can anyone give me a formula that will work like SUMIF, but instead of giving the SUM, gives the MIN instead, all [other] things being equal?

Example:
=SUMIF(A:A,A7,Q:Q)    << returns the sum of values in column Q
                           ...

Hi,

I have a excel workbook with three sheets. The first sheet looks up data in the second and third sheets.

Basically I want to be able to key in a number in sheet 1 (range B10- B44 as below) and if that number exists in the sheet 2 (G...

I've found this function that I use for uploading a database file to a FTP server, and it works fine. But I need to have an additional parameter in the function, so that after uploading the file to the folder indicated by the sDir paramter, it...

Hello All,

I had a question – I am vlooking up stuff. There are 11000 item rows. Now, I was wondering which is faster? – [vlookup text values to text columns] OR [vlookup  number values to numbers column]
Text value = 3213235645
Number value...

Is there a formula or addin in this version of excel to convert from julian dates to calendar date

example
734561
734561
734561
734561
734561

My paste options have disappeared in Excel 2010.  My laptop crashed earlier this week.  I just finished re-installing Windows 7, clean installation.  I re-installed Office 2010 and started working on a simple spreadsheet from the default...

I'm trying to use a command line tool called pdf toolkit to merge files in VBA.
I've successfully run it in a command window so I thought it would be easy to call a shell to run it in vba.
 

This runs without error but does not create the...

Hello

I have a large excel spreadsheet which contains a list of computer names and IP Addresses

Is there a way to have excel Ping each computer name and then return a result of "Online" or "offline" in a new column next to each computer...

Hi I have an excel table that looks something like this

 

What I need to do is could the distinct values in A for each grouping of B

So the out put needs to look like

 

Any ideas how I can accomplish this in Excel 2003?

Thanks

A worksheet has a shape, say a rectangle, selected.
In the formula bar there is a cell reference, say =$B$9.
Suppose B9 has a value of 30000 and B9 contains a formula =SUM(B2:B8) .
The rectangle will show "30000". (Whether selected or not)...

In Office 2010, when I delete text in Word or delete the contents of a cell in Excel, a lower case "x" appears after a second or two.  Sometimes several x's will appear.  It does not happen every time but fairly regularly.  This has been...

I have the below where I am trying to TRIM the data in the column.  For some reason, when it is downloaded, teh userid always has leading spaces, usually four.  But while the code loops through the column it does not trim the string in the cell. ...

Hi Folks,

I've got a series of data in an excel column. I want to create an additional column that shows an exponentially weighted moving average. Actually I want to see one covering around the latest 40 points, and one covering around the...

This program really changed from the XP version.  I'm currently having problems in two areas;

1) How can I put two workbooks side by side on the desktop?

2) How can I use "paste special" or "paste values"

Thanks

I am looking for a solution to the following problem:

We run scheduled reports overnight using Jet Reports. Jet Reports work with Excel, so that the report is saved as an Excel Workbook.

The problem is that, when people view a given report,...