Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Harold from Tulsa asked how to write a macro to simulate double-clicking the fill handle. Episode 1152 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
In Episode 1150, I wanted to add data bars to a series of words. Episode 1150 shows you how.
Labels: conditional formatting, data bar, format, VBA
posted by Bill at 9:00 AM
Someone put a bunch of mathematical calculations in column A as text and we want to evaluate those formulas. Mike and Bill provide dueling ways to solve this problem in Episode 1149.
posted by Bill at 9:00 AM
A cool way to streamline a VBA loop using SpecialCells. Episode 1140 shows you how.
Labels: formula, Go To Special, VBA
posted by Bill at 9:00 AM
External data pasted into Excel shows up with 1 row in columns A & C, but multiple rows in B. Today's dueling Excel Episode 1119 shows how to deal with this data.
posted by Bill at 9:00 AM
Roy asks how to prevent a workbook from being saved until the workbook is complete. Episode 1118 will show you the four lines of VBA needed to make this happen.
posted by Bill at 9:00 AM
John asks if there is a way to automate the process from Episode 322 of adding pop-up pictures to a cell. Today's Episode 1108 takes a look at the VBA code to add pop-up pictures to many cells.
posted by Bill at 9:00 AM
Your recorded macros might not work if someone renames a worksheet. Rather than use the worksheet name, use the code name for the worksheet. This name can never be changed and is more reliable. Episode 1096 shows you how.
posted by Bill at 9:00 AM
|
2 comments
LearnAccessByCrystal sends in a cool tip that will allow you to format dates to show quarters, weeks, and more. Episode 1074 shows you how.
posted by Bill at 9:00 AM
Uma wants to create many worksheets in a new workbook that are named after values in range B2:B20. Episode 1072 shows a VBA macro that makes all of this possible.
posted by Bill at 9:00 AM
Terry from Springfield, MO asks about a quicker, one-click way to change the Move Selection After Enter direction. Episode 1071 show you how to solve this problem by recording a couple of quick macros.
posted by Bill at 9:00 AM
After yesterday’s podcast about ASTM E29 rounding, I produce a function in VBA that will correctly do the bankers rounding algorithm in Excel. Episode 1047 shows you how.
posted by Bill at 9:00 AM
Izhak asks how to add a scrollbar to a worksheet for selecting a percentage. In this dueling Excel podcast Episode 1045, Bill and Mike will show you two ways to solve the problem.
Labels: ActiveX, forms control, VBA
posted by Bill at 9:00 AM
If your fiscal year does not end on December 31, then all of the cool date grouping functions available in pivot tables will not work for you. In Episode 1033, you will see how to add a couple of user-defined functions to your personal macro workbook to simplify the conversion of date to Fiscal Quarter or Fiscal Year.
posted by Bill at 9:00 AM
While yesterday's podcast was created with the macro recorder, you can switch over to the VBA editor to change 4 lines of the macro. This will create a macro that will work without requiring you to rename the pivot table. Episode 1028 shows you how.
Labels: Pivot Table, VBA
posted by Bill at 9:00 AM
Cheryl asks how to format all new pivot tables that she creates. The macro recorder can handle this, if you change the name of the pivot table before recording the macro. Episode 1027 shows you how.
Labels: Macro, Pivot Table, VBA
posted by Bill at 9:00 AM
Figure out the number of billable days between two dates. Episode 1018 looks at ways to count the number of days, number of workdays, or number of Monday-Wednesday-Friday dates between two dates.
posted by Bill at 9:00 AM
Shawn sends in today's question. The spreadsheet has billing data to audit. For each record, there is a location, a start date, and an end date. Shawn wants to find any records where the same date and location is double-billed. A quick two-loop macro solves this problem. Episode 1017 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
A question from YouTube asks how to write a macro to cut selected rows to Sheet2, adding a completion date. Episode 973 shows you how to write this short macro.
Labels: VBA
posted by Bill at 9:00 AM
Dave from San Francisco suggests you should use Excel without the mouse and challenges you to convert a formula to values in three keystrokes. Episode 958 shows various attempts to solve the problem without using the mouse.
Labels: keyboard shortcuts, OnKey, VBA
posted by Bill at 9:00 AM
Episode 954 brought an avalanche of mail on three fronts. (1) SendKeys is really cool, how do you do Alt instead of Ctrl? (2) Why the macro when you could select Entire Workbook in the dialog? (3) Application.Dialogs would have worked, the dialog is misnamed. Episode 957 discusses all of these tips.
posted by Bill at 9:00 AM
Laura asks how to display the Find dialog in a macro. The macro recorder won't do it. Application.Dialogs().Show won't do it. Episode 954 shows you a kludge to solve this problem.
posted by Bill at 9:00 AM
In Episode 943, I took data from Word and pasted to Excel for sorting. Now, I need to "type" that data back into Word, using the proper Style for each paragraph. Episode 944 shows you an Excel macro to automate this process.
Labels: Microsoft Word, VBA
posted by Bill at 9:00 AM
Bill (me) asks... is there any way to sort paragraphs in Word? I still don't know the answer, but in Episode 943, I show you how to take the data to Excel, add a few new columns and successfully sort groups of records in Excel.
Labels: Excel, Microsoft Word, VBA
posted by Bill at 9:00 AM
Pablo sends in a great question: why can't you copy a range of cells and paste only the total of those cells in a new cell? Microsoft should really add this functionality, but in the meantime, 2 lines of macro code solve the problem. Episode 936 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
Mike from OK City asks how to delete all names in a workbook. In Episode 904, I will show you a way to delete all names or to delete only the invalid names.
Labels: VBA
posted by Bill at 9:00 AM
You've probably seen the AC/DC video running in an Excel spreadsheet. The author did some cool tricks to deliver the WAV file to your hard drive. In Episode 891, I'll take a look at the code to extract an object from a workbook.
posted by Bill at 9:00 AM
Rick asks how to get started with VBA. In Episode 873, I show a horribly formatted file that someone in my Power Excel audience has to deal with daily. A macro seems like a good way to deal with the file. I will show you how to turn on the macro recorder, turn on relative reference, and record your first macro.
Labels: VBA
posted by Bill at 9:00 AM
Someone tried to record a macro to find the records from a certain date. Since finding dates requires the date format to match exactly, a better way to go would be using Advanced Filter. In Episode 871, I show how to use AdvancedFilter in a macro to extract records from a certain date.
Labels: Advanced Filter, VBA
posted by Bill at 9:00 AM
Sergiy asks if there is any way to have a cell comment or a validation input box reflect a value from a formula in a cell. I couldn't find a good solution, other than using VBA. Episode 855 shows you how.
posted by Bill at 9:00 AM
Shawn sends in a great tip for today's podcast. Access has a Filter by Selection icon. Using a few lines of VBA code, you can add this functionality to your personal macro workbook. Episode 839 shows you how.
Labels: Filter by Selection, VBA
posted by Bill at 9:00 AM
So, you upgrade to Excel 2007 and your macros stop working. If you are incredibly lucky, it might be a situation where the macro would work, but you need to enable macros. Episode 828 shows where Microsoft hid Macro Security options and what the new words actually mean.
posted by Bill at 9:00 AM
Mark from New Hampshire notes that the macro recorder can not record the simple act of pressing the AutoSum button. In Episode 823, I show you the arcane workaround to solve the problem.
posted by Bill at 9:00 AM
After all of these episodes about using the macro recorder, Jonathan tried to record a macro to rename worksheets. The code fails sometimes and works other times. We will take a look at that code and understand how to generalize it to have it always work in Episode 814.
Labels: VBA
posted by Bill at 9:00 AM
|
0 comments
Ever wonder how MrExcel spends his free time? He watches the macro recorder write code before his eyes. In Episode 813, learn how to arrange the VBA window and the Excel window to watch which lines of code appear in response to actions in Excel.
posted by Bill at 9:00 AM
Today, a closer look at the macro from Episode 811. While it LOOKED like it worked, the recorded macro leaves a lot to be desired. Your totals will be wrong if you rely on the AutoSum button during macro recording. Episode 812 shows you the workaround.
Labels: VBA
posted by Bill at 9:00 AM
Part 1 of 2: Many times, you have to use a clever mix of relative and absolute recording to get the macro to perform certain tasks. The goal today is to navigate to the bottom of a data set, add totals, and then move back to row 1. Episode 811 will show you how to handle the relative button, but watch out, as episode 812 will reveal yet another problem.
Labels: VBA
posted by Bill at 9:00 AM
While yesterday's macro showed a simple formatting macro, it had a limitation that you can not move to a new cell. That is a fairly severe limitation. In Episode 810, we take a look at why moving the cell pointer causes a macro to fail and the simple setting to allow the macros to work.
Labels: VBA
posted by Bill at 9:00 AM
Record a simple macro in Excel 2007. In Episode 809, we take a look at a simple macro that the macro recorder can reliably record. The trick is that you never move the cell pointer during the recording of the macro.
Labels: VBA
posted by Bill at 9:00 AM
In our second Where is it Wednesday episode, we take a look at how to unlock all of the macro functionality in Excel 2007. Episode 808 shows you how.
posted by Bill at 9:00 AM
Pat needs to find duplicate addresses. However, the addresses are typed differently, data is in different columns, it is a real mess.
Labels: VBA
posted by Bill at 9:00 AM
Go to a cell in Excel and enter July 2008 as 07/08. Unfortunately, Excel will convert this to July 8 of the current year. In Episode 798, a bit of VBA code to convert that entry back into a month and year.
Labels: VBA
posted by Bill at 9:00 AM
Episode 322 talked about adding pop-up pictures to a cell. Devin asks if there is a way to add many pictures at once. In Episode 785, I use some macro code from page 764 in the book to quickly add pop-up pictures to many cells.
posted by Bill at 9:00 AM
Do you have the world's greatest Personal.xls and want to share it with others in your company? Watch Episode 780 for the steps necessary to distribute the personal macro workbook.
Labels: Personal Macro Workbook, VBA
posted by Bill at 9:00 AM
Column I is sparsely filled in with product codes. You would like a macro to copy only the non-blank cells to a new column. It turns out that the macro recorder can actually reliably perform this task. Episode 779 will show you how.
Labels: VBA
posted by Bill at 9:00 AM
Pau asks how to take a CSV file from one website and convert it for importing to Google Calendar. In Episode 773, I turn on the macro recorder and attempt to fix the file.
posted by Bill at 9:00 AM
Telling red cells from green cells in Excel is difficult for those suffering from color blindness. In Episode 771, I take a look at a macro that will show the text name of the color in the status bar for any selected cell.
Labels: VBA
posted by Bill at 9:00 AM
Rene from Norway asks how to modify the code from podcast 730 in order to create a new workbook for every department. Episode 754 shows you how.
posted by Bill at 9:00 AM
If you want the macro from Episode 744 to always be available, you can save it as an add-in and install the add-in. Episode 745 will show you how.
posted by Bill at 9:00 AM
If you want to randomly choose from a list and never have duplicates, you can use the method discussed in this podcast. Episode 734 shows you how.
posted by Bill at 9:00 AM
Hamilton asks how to generate a column of bingo numbers, one at a time. In Episode 733, a tiny macro will add random numbers down column A.
posted by Bill at 9:00 AM
J.B. needs to create a new worksheet for every customer listed on the summary worksheet. He asks if there is an Insert Worksheets command. While there isn't, you can create one with a few lines of VBA code. Episode 730 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
Back in Episode 695, I used the InputBox function in VBA to ask for a couple of answers. In today's podcast, I will show you how to convert that macro to use a custom user form in Excel. Episode 701 shows you how.
posted by Bill at 9:00 AM
In today's podcast, a five line macro that will allow you to scale a selected range. This macro was inspired by engineers in one of my recent VBA seminars. They had been using a similar tool for years and we were surprised it was a five line macro to replicate the tool. Episode 695 shows you how.
Labels: Macro, Scale Numbers, VBA
posted by Bill at 9:00 AM
Microsoft hides some of the VBA controls in Excel 2007. Episode 687 will show you how to access the Developer tab in the Excel 2007 ribbon.
Labels: Excel 2007, VBA
posted by Bill at 9:00 AM
Marshall from Pennsylvania watched Episode 680 from last Friday and came back with a cool use for the same technology: scoring runners in a 5K or 10K race. In Episode 685, we take a look at a simple system in Excel to track the finishing times as you type in bib numbers.
Labels: event handlers, time calculations, VBA
posted by Bill at 9:00 AM
Florian wants to time stamp a record every time someone types a new name in column A, but the NOW function is not working. In Episode 680, I will show you a few lines of VBA code to solve this problem.
Labels: Event Handler, NOW, timestamp, VBA, Worksheet_Change
posted by Bill at 9:00 AM
Paul uses the macro recorder to record the action of naming the current region. The macro recorder never gets this correct. There is a far simpler version of code to create named ranges. Episode 676 will show you how to replace the recorded code.
Labels: Named Ranges, VBA
posted by Bill at 9:00 AM
Today, the questioner asks how to unprotect the green cells. There isn’t any good way to do this in the Excel user interface, so in Episode 593, I use a tiny bit of VBA code to achieve the effect.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Sub UnProtectGreen
For each Cell in Selection
If Cell.Interior.ColorIndex = 4 then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
End Sub
Labels: VBA
posted by Bill at 9:00 AM
Back in Episode 578, I used an incredibly complex method using NotePad for joining cells together. Today, Jonathan from the UK points out that a few lines of VBA code would have made the solution incredibly simple. Episode 587 shows you how.
Function ConcatenateRange(rCells As Range)
Dim vTemp As Variant
Application.Volatile
For Each vTemp In rCells
ConcatenateRange = ConcatenateRange & vTemp & " "
Next vTemp
End Function
Labels: VBA
posted by Bill at 9:00 AM
In honor of Memorial Day in the United States, a question sent in from a naval aviator who has to enter a series of time values. Using a tiny bit of VBA code, we can eliminate the need to type the colon in the middle of the time value. Even if you don’t have to enter times all day, the techniques in Episode 516 can ease the redundant data entry tasks.
Labels: Event Handler, Time, VBA
posted by Bill at 9:00 AM
George sends in a question – how can you convert a number to words? For example, how can you write $1234.56 as One Thousand Two Hundred Thirty Four dollars and Fifty Six cents. Although many utility packs offer this functionality, the VBA code is actually available for free from Microsoft. Episode 499 shows you how to add the code to your workbook. Be sure to stop back tomorrow for episode #500, when we will give away a treasure trove of prizes.
Labels: VBA
posted by Bill at 9:00 AM
Mudit asks, is there any way to sum just the bold cells in a range? This problem is hard to solve in Excel, but easy if you use a tiny user defined function in VBA. In Episode 490, learn how to find free user defined functions on the internet and paste them into your Excel workbook.
Labels: VBA
posted by Bill at 9:00 AM
In offices throughout the US, people are starting to turn in their NCAA bracket sheets to the office commissioner. Whether you are asking accountants to fill out an NCAA bracket or sales reps to fill out a sales forecast, it makes sense to collect the data in an Excel workbook. In Episode 464, we take a look at a macro that can be used to gather entries from the individual worksheets and combine them into a master worksheet.
posted by Bill at 9:00 AM
Someone posed this question in a recent Power Excel seminar - he had to format cells with a strange format, and had to do this repeatedly. Learn how to record a simple macro to automate this task. Episode 384 shows you how.
posted by Bill at 9:00 AM
Back in August, the podcast showed a trick for preventing people from going outside a certain range of the worksheet. Today, a caller points out that this setting is lost when you close and re-open the workbook. Today's postcast shows how to create a simple one-line macro using Worksheets("Sheet1").ScrollArea = "A1:J10" to ensure that the setting always comes back. Episode 365 shows you how.
posted by Bill at 9:00 AM
