Kristie asks "I am trying to convert one column that has both last name and first name (LAST NAME, FIRST NAME M.I.) in it to two columns, one with last name and one with first name. Is there a way to do this?"

To do this, use Text to Columns. Select the column where you have the names, go to Data, Text to Columns, select Delimited in Step 1, in Step 2 make sure that the comma checkbox is activated. Finally, in step 3 select a Destination cell (It defaults to the current cell). Hit Finish and that's it.

Daniel ask "Im just trying to find out how I can add 50% to each number in a selected range of numbers. I need each number to have 50% added/ x 1.5."

This one is easy. In an empty cell put 1.5 or 150%, copy it (Control C), then, select the range of numbers you want to increase, go to Edit, Paste Special, Values (or formulas, up to you), and also choose Multiply. Click [OK] and DONE !

That easy !

Today, another VBA tip. Sometimes, the InputBox function just isn't enough... you need to let the user point to a cell, instead of having to write in what you want. This cannot be done using this Inputbox, but, can be done using Excel's Inputbox, as follows:

Application.Inputbox(Prompt:="Choose a cell",Title:="Choose",Type:=8)

This returns False if canceled, or a reference to the cell/range selected. That way, you could set it to a variable, like

Set MyRange = Application.Inputbox(.....)

You can see the Online help for more Types of this Inputbox.

Mike asks "How can I modify the background color of a cell using VBA ?"

You have to use the Interior property of cells, and then, you can use either ColorIndex (To use one of the 56 "presets" colors in Excel) or Color, like this

ActiveCell.Interior.ColorIndex = 36

or

Range("A1:A6").Interior.Color = RGB(200,160,35)

Robert asks Could you tell me how to break an expression of Msgbox into 3 lines and insert a blank line into such an expresion.

Use this code:

Msg = "This is line 1" & vbcr & "This is Line 2 " & vbcr & vbcr & "This is Line 4"
Msgbox Prompt:=Msg

One good use of Data Validation comes when there's a need to "link" two or more comboboxes, making them, dependant.

For example, John asks "I have a list of countries (USA, Australia, England), and a list of cities. How can I make than when the user selects USA in one cell, in the other one appear only cities from USA (New York, Los Angeles), and also for Australia (Camberra, Perth), etc. ?"

The first thing that needs to be done is name the lists. Select the countries, without the title, and name it COUNTRIES. Next, select all cities (Again without titles) from USA and name it USA. Continue this process for every country.

Now, let's assume that the first list appears in A1. Go to Data, Validation, Select List, and in "Source" put

=COUNTRIES.

Make sure that "In-cell dropdown" is checked.

Now, if the second list appears in B1, go to Data, Validation, again Select List, and in "Source" put

=INDIRECT(A1)

If Excel returns an error, don't worry, you don't have anything selected in A1. To test it, select a country in A1, and now, when you click in B1 you should see only the cities from that country.

Addition. Ian Narbeth asked to remind that Defined Names can't include spaces, so, to enter, Great Britain, you would have to put it like "Great_Britain" or "GreatBritain". Thanks for that.

Extracted from MrExcel.com Message Board

Here's a question I see often. "I'm trying to use the RANK function to sort the values in Column A, but, the problem arises when I have two or more cells with the same value. Then, the RANK also repeats some positions. For example, if I have, 50, 60, 62, 62, 67 in Column A I would get 5,4,2,2,1 in Column B. Is there a way I can get 5,4,2,3,1 ?"

To do this you need a combination of RANK and COUNTIF as follows. If the data is in A2:A10, the formula in B2 would be:

=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1

and drag down till B10.

Jenny asks "Is there a quick way of adding every other cell ?, I have a column of about 250 cells, and I have to select them manually using Ctrl. Can you help ?"

Jenny, this is a matter of using a little math to remember how to determine if a number is even or is odd, and then translate that into the formula.

The formula
=MOD(A1,2)
would return 0 if the number is even and 1 if odd. If we use instead of A1, the ROW() function, we can know if a particular row is even or odd. Now, suppose you're trying to calculate this formula

=A1+A3+A5+A7+A9 ... 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 non-array formula

=SUMPRODUCT((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))

PPD asks"How can I format a cell that has data in seconds to show it as hours:minutes:seconds?

For example, if I have 16548 seconds it should show 4:35:48"

First, it's important to remember that Excel keeps dates informatios as day fractions. Therefor, to show the value it first needs to be converted like this:

Divide el value in seconds (16548) in (60 s / min * 60 min/hr * 24 hr/day) to get

=16548/(24*60*60)
or
=A1/(24*60*60)

This result can then be formatted as hh:mm:ss

Here's a helpful tip for VBA programmers using Office 2000 or XP.

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 change that needs to be made to the Registry. It's very important to make a backup of the registry before atemping any changes to it. If you don't know how to do this, don't try this tip. MrExcel.com will not be held responsible for any consequences as a result of this change..

Enter Regedit, and look for the key HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Common\HelpViewer in Office 2000 or HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\HelpViewer in Office XP

If there isn't a variable called 'IsFloating', it needs to be created (It's a DWORD) and assign it a value of 1.

Jorge asks "Please, how can I fill a column with the last date of the month, starting with the last date of one particular month ?. I've tried filling series, but if I start for instance with 30-june-2002, the following cell will be 30-july-2002 but not 31-july-2002 that is the one I need, and so on. Thank you,"

To do this you’ll have to use a formula. If the starting date (30-june-2002) is in A2, put this formula in A3 and drag down.

=DATE(YEAR(A2),MONTH(A2)+2,0)

Extracted from MrExcel.com Message Board

John Munoz asked "I'm trying to calculate the median of salaries with a particular job code within a large set of data...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 A2:A100 and Salaries are in B2:B100. This pretty straightforward formula would give the expected results:

=MEDIAN(IF(A2:A100="JobCode",B2:B100))

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.

Kate asks "Is there anyway in Excel that I can round numbers to the nearest 500 or 1000 ?"

This is one function that I like very much ! If the number is in A2 just use this formula

=CEILING(A2,500)

Extracted from MrExcel.com Message Board

One common issue I face is "How can I have VLOOKUP return a 0 or a empty string "" instead of #N/A! ?"

Let's assume that the current formula is

=VLOOKUP(A1,$B$2:$D$100,3,False)
or more condensed
=VLOOKUP(A1,$B$2:$D$100,3,0)

one approach to do this is as follows

=IF(ISNA(VLOOKUP(A1,$B$2:$D$100,3,0)),””, VLOOKUP(A1,$B$2:$D$100,3,0))

but this requires Excel calculating TWICE the VLOOKUP formula which is “expensive” in terms of efficiency.

One improved method is

=IF(COUNTIF($B$2:$B$100,A1), VLOOKUP(A1,$B$2:$D$100,3,0),””)

This way the VLOOKUP is only calculated if the value in A1 exists in B2:B100, and therefor, VLOOKUP won’t return a #N/A!

Kevin asks "can I move from cell to another (and skip some cells) by pressing the tab key or some other key?"

Lock all cells, unlock those cells where the user will enter data and then protect the sheet. That way the use can press TAB to move between unprotected cells.

You could also set the EnableSelection property of the sheet to 1 – xlUnlockedCells to prevent the user selecting anything not allowed. This will only work when the sheet IS protected.

This is my first tip of the day, and with it, i'll try to face one common question using VBA in Excel. That is "how can I find the last used row in a particular sheet ?". There are several methods to accomplish this, some more exact than others, but they can all serve your needs.

One common method

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

which 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).Row

but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.

A couple extra methods are more reliable.

LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count

This methods can be used on any sheet, not just the active sheet.

GH asks "I'm facing a problem today, I have a user form with 5 different text boxes, 3 of those text boxes are mandatory. If I don't put nothing in one of those text boxes and I click the command button to perform the verification, I have a message box that appears and tells me that the text box "XXXX" is mandatory. When I click OK on this message box I would like my VBA project to select/activate the text box where data is mandatory."

Use the .SetFocus command:

Me.TextBox3.SetFocus

Chris asks: How do I add the number of cells in a column and then divide into the dollar total of that column to get my percentage close ratio?

To count the number of numeric cells in a column, use =COUNT(A1:A99).
To count the number of alpha or numeric cells in a column, use =COUNTA(A1:A99).
You can combine functions in a formula: =SUM(A1:A99)/COUNTA(A1:A99)