Home > Excel Help Portal > Programming Excel

<<  (Page 17)  Previous              Next  (Page 19)  >>

Programming Excel

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses ways to program
Microsoft Excel using VBA.

851. Circular Reference Does Not Calculate During Range.Calculate Execution When you use the Range.Calculate method or the Rows.Calculate method in Microsoft Visual Basic for Applications (VBA), and the range contains all the cells in a circular reference, the calculation does not occur and there is no error message... KB#827990. Keywords: Errors, VBA, Circular References, Error Message

 
852. Graphing If you create a chart in a worksheet, you may find that Excel sets this option by default. You may find that you don't like some of the chart options set by Excel. The great news is that everything in a chart is customizable. All of those settings are just a right-click away.In this case, go to the... Keywords: Charts, Format, VBA, Web

 
853. Logical Expressions Keywords: VBA, Web, Comments

 
854. Excel Stops Responding When You Click Help Topic In Microsoft Excel Help, when you search on the keyword VBA, and then click the Help topic Get Visual Basic Help in Microsoft Excel , Excel may stop... KB#248867. Keywords: VBA

 
855. Helpful Keyboard shortcuts common to Excel Control + "C": CopyControl + "X": CutControl + "V": PasteF2: Edit current cell (Makes cell references appear in colors for easy editing)F5: Go toF11: "Instant" chart.Shift + F3: Paste function wizardControl + F3: Define NameControl + "+": Insert Cells, Rows, Columns. Depends on the current... Keywords: Charts, Borders, Colors, Format, VBA, Web

 
856. Differences in Excel versions I could follow your theory if the situation were reversed. But, anything that you know how to do in Excel 97 should have translated directly to Excel 2000. Microsoft is pretty good about making sure that features from one version work in the next version. The one major exception that I can think of... Keywords: VBA, Web

 
857. Text value response to input Sure, Charlie, it would take a simple If formula. These come in real handy the next time you have a chance to pull off an incredible upset. Try a simple formula like this in cell A1:=IF(Bartolo="Weary","Absolutely Pull Him Out of the Game","Pull Him Out of the Game Anyway")Congratulations to all... Keywords: SERIES, Formulas, VBA, Web

 
858. Copying Subtotaled Data Great question. When you use the Group and Outline buttons (or even the AutoFilter) to hide certain rows and then try to copy them, you find that Excel copies all of the hidden rows as well. Luckily, there is an easy, if not intuitive, solution to the problem.First, highlight all of the totals like... Keywords: Outline, VBA, Web

 
859. An Excel VBA function to display cell contents In the example above, the user defined 'FormText()' function is used in cells P21 and P22 to display the contents of N21 and N22. The macro shown here should be entered into a Visual Basic module, either in specific workbook or in your (hidden) Personal.xls macro workbook. It can then be used like... Keywords: Formulas, VBA, Macro

 
860. Merging rows I will soon have about 400 spreadsheets with up to 6000 rows each in which I will have to apply the above process. And of course this has to be done in a three day turn-around period. Can you help??????Yes. You need to highlight all 6000 rows. Edit - GoTo. Click the Special button. Click Blanks and... Keywords: Formulas, VBA, Web

 
861. Increment a Hour/Minute String Keywords: Formulas, Links, Printing, VBA, Macro

 
862. Solver.xla Is Missing the VBA Functions Solver.xla is missing Microsoft Visual Basic for Applications (VBA) functions in French versions of Excel 2002. Macros that use Solver.xla do not run after you upgrade to Excel... KB#320302. Keywords: Solver, VBA, File Name, Files

 
863. Excel select Random numbers from a column There are several approaches to this. Here is one way to accomplish this. Let's say that your 50 numbers are in cells A2:A51.Highlight cells B2:B51Enter the formula =RAND() and hit Ctrl+Enter. This will enter the formula in all 50 cells at onceCopy B2:B51 with Ctrl+cPasteSpecial the formulas into... Keywords: Rank, Sorting, Formulas, VBA, Web, RAND

 
864. Formulas Calculate Incorrectly After Changing Worksheet Name When you recalculate your worksheet, some formulas may calculate incorrect... KB#248174. Keywords: Sorting, Formulas, VBA

 
865. Keep Excel in focus Jose Luis asks "When I call Help without using the assistant (F1) Excel resizes itself to allow the Help window and the spreadsheet to be visible at the same time. Is there anything I can do to prevent this ? I want Excel to stay maximized and so the help file."To modify this behavior there's one... Keywords: VBA, Registry, Web, Window

 
866. Highlight the active cell What a cool question. The trick is to use the Worksheet_Selection change event instead of the Worksheet_Change event. This special event handler macro has to be entered on the code pane associated with your particular worksheet. A discussion of how to find this in the project pane is at this... Keywords: Colors, Macro, VBA, Web

 
867. Identify Cells with Formulas Juan Pablo, the Excel whiz behind our Spanish-language MrExcel Consultoria Division, came up with this cool trick that relies on XLM:One *quick* method to do this is as follows.Select A1.Go to Insert, Names, Define.Put a name in it, like HasFormin the refers to put:=GET.CELL(48,A1)Now, select the... Keywords: Format, Patterns, Formulas, VBA, Web

 
868. Round time to the nearest quarter hour MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: SERIES, Custom Number Formats, Formulas, VBA, Web, ROUND

 
869. Removing or avoiding automatic hyperlinks You may have discovered that Excel 2000 supports automatic cell hyperlinks. Whenever you type something that resembles a URL or an e-mail address into a cell, this feature automatically converts the text into a clickable hyperlink. But what if you don't want to create a hyperlink? If you use Excel... Keywords: Charts, AutoCorrect, Format, Formulas, Links, Printing, VBA, Macro, Hyperlinks

 
870. Changing #N/A to "Your Response" text Vlookup is a powerful tool. Sometimes, if an exact match is not found, a value other than the standard #N/A is desirable. For example, if your look up range (table_array) is A1:C10, the formula VLOOKUP("cat",$A$1:$C$10,3,FALSE)will return #N/A if cat is not found in A1:A10. If instead you wanted to... Keywords: Formulas, VBA, Web, VLOOKUP

 
871. Compare List and Insert if Not Found This thing skims thru the list and if a code is not found, it makes a row and inserts it. The NumbAddits is used in another process to determine where to put totals. KLM = UBound(AbbrevArray) 'How many Abbrevs BOAC = UBound(LongNameArray) 'How many names Open DataPath & "JobLists.TXT" For Output As... Keywords: Arrays, Formulas, Links, Printing, VBA

 
872. Creating Page Breaks in Excel-automaticaly This macro will solve the problem. For some reason, macros that change page breaks or page setups take a long time to run, but this will certainly be faster that doing the process manually. The macro assumes your data is in column A and that it starts in row... Keywords: Macro, VBA, Web

 
873. Quickly Navigate to Any worksheet MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: VBA, Web

 
874. Contextures -- Excel Chart Links Links to Samples and Articles Contact:... Keywords: Add-In, Charts, GANTT, SERIES, Whisker, Download, Links, Printing, Macro, VBA

 
875. No Error Message Returned to VBA on Failed Save During a file save operation in Microsoft Excel, if a second program locks the file, a temporary file with an eight-character hexadecimal name is created, which contains the saved data, and the original file is deleted. No error message is... KB#269521. Keywords: Errors, VBA, Error Message

 
876. Eliminate "/" from File Names when Saving with a Macro The problem arises when the format of the date includes the “/” character… try the following:ActiveWorkbook.SaveAs Application.Substitute(Range(“A38”).Text,”/”,”-“) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied,... Keywords: Format, VBA, File Name, Web, NOW

 
877. Inputbox to accept specific data This macro will continue to ask the user for a number between 1 and 10 or will stop if he/she... Keywords: Macro, VBA, Web, Validation

 
878. Create a unique record number Here is the hypothetical application that I am imagining. There is a workbook with an Invoice worksheet and a Menu worksheet. I propose storing the last invoice number on an out of the way cell on the Menu worksheet, lets say Z1. If the invoice number is supposed to go in cell H2 of the invoice... Keywords: Macro, VBA, Web

 
879. Increment Hours and Minutes in a String Someone in my department was showing me where they were trying to build a batch file that would show the times and string to be run every 5 minutes around the clock. I shot my mouth off and said I could write a 7 line macro that would do the trick. I eventually needed the “Select Case” statement to... Keywords: Printing, VBA, Macro

 
880. Count Occurences in String My first reaction was, "it can’t be done!, and then I figured out how to do it. Option Explicit Public daSting As String, Z As Long, daRow As Long Public stringLen, daAnsw,... Keywords: Formulas, Links, VBA

 
881. Changing grades (in numeric format) to Letter Grades John D = 83 which is a B-John D2= 85 B+ and so on a so forth."The solution is simple. Create a Table that looks something like this:0 F60 D70 C80 B90 AWhere you put the LOWER limit of each range. For example, F goes from 0 to 59, so you put a 0 and next to it, an "F". After this, select this Table,... Keywords: Formulas, VBA, Web, LOWER, VLOOKUP

 
882. Find the last row of data in a range One common methodLastRow = Cells.SpecialCells(xlCellTypeLastCell).Rowwhich is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.Another method to find the last used row in a particular column is:LastRowColA = Range("A65536").End(xlUp).Rowbut this doesn't tell... Keywords: VBA, Web

 
883. Adding every other cell in a column etc.or this one=SUM(A1,A3,A5,A7...)=SUM((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))Remember, this is a CSE Formula, to enter it you must press at the same time Control Shift Enter, instead of just Enter as regular formulas.You can also use this... Keywords: Formulas, VBA, Web, MOD, ROW

 
884. Find the elapsed time between two time entries MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: Days, Custom Number Formats, Formulas, VBA, Web, MAX, ABS

 
885. Creating proper names format There are two possibilities. =UPPER() will change the text to all upper case. =PROPER() will change the text to proper case.If cell A2 has the word "john smith", use these formulas:In B2: =UPPER(A2) to return JOHN SMITHIn C2: =PROPER(A2) to return John SmithCopy the formula from B2 down to all of... Keywords: Formulas, VBA, Web, UPPER, PROPER

 
886. Add Row while copying and manipulating data Here is a macro that will insert a row above the cellpointer and copy the contents of the prior row. Let's assume your data extends from A to T, and that you want to delete the contents of the new row's cells H, K, and MI'll use the keyword ActiveCell to make the macro work in relation to the cell... Keywords: Macro, VBA, Web

 
887. Advanced Formulas Tutorial Using a List Box to Change Data Displayed on a Worksheet Learn to use a List Box to update values displayed on your worksheet. Use the OFFSET function to return values from a table. Select an item in the List Box and see values for that item appear on a worksheet. IRR, XIRR, NPV, XNPV Functions You... Keywords: Add-In, AutoFilter, Criteria, Sorting, Sales, Arrays, Formulas, PivotTables, Iteration, VBA, Circular References, Auditing, OFFSET, XNPV, IRR, XIRR

 
888. Multiple Page Numbers The line for “Second” is the only thing to change as sheets are added or removed. Makes pretty footers with “Page 2 of 15”,... Keywords: Formulas, Links, Printing, VBA, Setup, Window

 
889. Create and name a new worksheet with VBA Just like this:Dim WS as WorksheetSet WS = Sheets.AddYou don't have to know where is it located, what's it's name, you just refer to it as WS.If you still want to do this the "old fashioned" way, try this:Sheets.Add.Name = "Test" MrExcel.com provides examples of Visual Basic procedures for... Keywords: Macro, VBA, Web

 
890. Removing text-string characters Mike asks "Is there any way I can simply remove certain characters from a text string. For example, I have a post code BN19 4GX and I want to remove all numeric charaters to leave BN GX."This is easier using VBA.Go to the VB Editor (Alt + F11), go to Insert, Module. Paste this code in... Keywords: Formulas, VBA, Web

 
891. Add today's entries to the next empty row on a master sheet Good question. Let's say the values are in cells C10 and E12 of Sheet1 and you want them appended to the next row in Sheet2. The real trick here is the code to find the next row on Sheet2. Starting at the last row in Sheet2 (A65536) and then using .End(xlup) will find the last row with data in... Keywords: Arrays, Macro, VBA, Web

 
892. CountIf or Sumif with two conditions MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: Criteria, Years, Arrays, Formulas, VBA, HTML, Web, COUNTIF

 
893. Eliminating the 'Enable Macros' messagebox in Excel Another possibility is a short VB6 program that would open the Excel file. If you open an Excel file with VB6, you eliminate the enable macros warning. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not... Keywords: Macro, VBA, Web

 
894. Dialing Phone Numbers the Windows 95 Dialer and Windows3.11 Cardfile Simple VBA macro that uses Shell and SendKeys commands to dial a phone number using the Windows95 dialer applet or Window 3.11 Cardfile Autodialer. You can use the Shell command in VBA to start the Dialer Windows application and dial a phone number with your computer's modem. The combination of the... Keywords: Download, Format, Macro, VBA, Window

 
895. Highlighting and Exporting Cell notes If you just want to print a cell note, you can use the File, Page Setup, Sheet, Print dialog and set Print Notes to ON. Excel will print the row and column heading for each note if you choose option Print Row and Column Headings in the Sheet dialog. If you want to highlight the cells with notes so... Keywords: Export, Borders, Printing, VBA, Macro

 
896. How to create a "Top 9" If your races are in A1:A11, try with this formula=IF(COUNT(A1:A11)9)*SMALL(A1:A11,1)-(COUNT(A1:A11)10)*SMALL(A1:A11,2)) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties... Keywords: SERIES, Formulas, VBA, Web

 
897. Using Custom Dialog Boxes I have been getting a lot of notes about filling a custom dialog box. I particularly like this method because it allows me to edit the list and use it without re-programming. The lines between the § symbols are the key to dynamically filling the list. '------- Print List chosen from... Keywords: Printing, VBA, Object

 
898. Custom Functions If Russell adds a new, temporary column to be used for sorting, he can use this new custom function IPForSort(). This code should be pasted into a code module in the... Keywords: Sorting, Format, VBA, Web

 
899. How to easily 'unmerge' cells When you have to insert a column and there are some merged cells, DO NOT select the entire column, instead, just select ONE NON-MERGED cell, then go to Edit, Insert, and select "Entire Column". That should work. MrExcel.com provides examples of Visual Basic procedures for illustration only, without... Keywords: VBA, Web

 
900. Finding an average between two data sets something like a Sumif function but to calculate the median. Is there a way to do this?"This is an issue that calls one of the great things in Excel: Array formulas (Or CSE Formulae, as refered here at MrExcel.com, check this tip for hints on CSE Formulae). Let's assume that the Job codes are in... Keywords: Arrays, Formulas, VBA, Web, MEDIAN

 

<< (Page 17)  Previous              Next  (Page 19) >>